Oct 22 2021 06:56 PM - edited Oct 22 2021 06:59 PM
I assume I'm making a basic error or overlooking something silly.
_test is a 5 column table with a column Bool. I want the first two columns only, for those rows where Bool is "Yes".
I have a function like this:
=LET(
matches,FILTER(_test,_test[Bool]="Yes","None"),
shows, OFFSET(matches,0,0,,2),
shows
)
I get a #value error. If I display matches instead of shows, I see my 2 rows.
If I break it up into two separate calculations, I get the correct result:
=LET(
matches,FILTER(_test,_test[Bool]="Yes","None"),
matches
)
gives me two rows, and then (result being in C10)
=LET(
shows,OFFSET(C10#,0,0,,2),
shows)
gives me the two column result I wanted.
Why doesn't OFFSET(FILTER()) work, but OFFSET(materialized filter data) does?
Oct 23 2021 03:19 AM
SolutionOFFSET() requires range as first parameter, not an array. Instead you may use something like
=LET(
matches,FILTER(_test,_test[Bool]="Yes","None"),
shows, OFFSET(matches,0,0,,2),
one, FILTER(matches, {1,1,0,0,0} ),
two, INDEX( matches, SEQUENCE(ROWS(matches)), {1,2}),
two
)
Oct 23 2021 03:19 AM
SolutionOFFSET() requires range as first parameter, not an array. Instead you may use something like
=LET(
matches,FILTER(_test,_test[Bool]="Yes","None"),
shows, OFFSET(matches,0,0,,2),
one, FILTER(matches, {1,1,0,0,0} ),
two, INDEX( matches, SEQUENCE(ROWS(matches)), {1,2}),
two
)