Hello,
How can i build a slice with multiple row filter condition ?
Exemple, i would like to show only โnoโ of a column containing โyesโ, โnoโ, โmaybeโ.
What correct expression(s) i have to use ?
Thanks
Solved! Go to Solution.
OR([Column] = "yes", [Column] = "its ok", [Column] = "for sure")
([Column] = "no")
would appear to be all you need based on your vague question.
Ahah ! Sorry. I reread myself and indeed, my question is weird.
I specify : imagine i have X possibles row filter condition (โmaybeโ, โone dayโ, โtoo lateโ, โno of courseโ, โฆ). On these, i just want to show Y (โyesโ, โits okโ, โfor sureโ).
What is the correct expression ?
OR([Column] = "yes", [Column] = "its ok", [Column] = "for sure")
Itโs ok !
Thanks all
No Problem, appsheet is great. you will see.
Maybe:
AND(
[Column] = "yes",
[Column] = "its ok",
[Column] = "for sure"
)
Another approach:
IN([Column], {"yes", "its ok", "for sure"})
Hey Steve, How would you take a sentence of words and split each word into list? This could be the solution to my question.
You could try splitting on a space: SPLIT([Column], " ")
. The problem with that is punctuation would fall in with words. So SPLIT("Hi, my name is Steve!", " ")
would give:
Hi,
my
name
is
Steve.
Notice the punctuation in Hi,
and Steve.
.
A bit of a twist on thisโฆiโm trying to use a value in another table to determine if the rows should be included in the slice. Basic terms: column contains values of A,B,C,D
If count A>X then exclude A
If count B>X then exclude B
if count C>X then exclude Cโฆ
โฆyou get the picture. The counts are located on another table and am using Lookup to grab these values and compare to a hardcoded #.
It feels like a simple solution but for some reason I canโt solve for all of the combinations. Is the filter in a Slice additive in this way?
now that iโve laid it out, will this workโฆ
And(isblank([useremail column],
And(if (A>X,[letter col]<>โAโ,[letter col]=โAโ),
if (B>X,[letter col]<>โBโ,[letter col]=โBโ),
if (C>X,[letter col]<>โCโ,[letter col]=โCโ),
if (D>X,[letter col]<>โDโ,[letter col]=โDโ)
)
)
What is A? What is the count of A?
Please share this expression so we can get some idea of what youโre doing.
What do you mean by additive?
Sorry. I was trying to simplify the expression. I have been having a hard time reading through alot of these posts when every example is using different table and column names. I have to decipher the nomenclature before I can figure out the logic. Here is my attempt at making it work:
AND
(isblank([Booked By]),
AND(
if(LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWEP YTDโ)>=12,[Sail Type]<>โWEPโ,[Sail Type]=โWEPโ),
if(LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWEA YTDโ)>=12,[Sail Type]<>โWEAโ,[Sail Type]=โWEAโ),
if(LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWA YTDโ)>=36,[Sail Type]<>โWAโ,[Sail Type]=โWAโ),
if(LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWP YTDโ)>=24,[Sail Type]<>โWPโ,[Sail Type]=โWPโ)
))
Maybe try explaining, in plain english, what the criteria should be for the rows in your slice. Your syntax looks correct, does it return the results you expect? My guess, without knowing your criteria, is that very few records, if any, would be able to meet ALL of these conditions.
You can get rid of one of the AND()s. AND() inside of AND() is redundant.
If an owner is over his allocation of time slots then I would like to exclude these from the list. There are 4 types of time slots. WA, WP, WEA, WEP. Each owner is given a certain number of time slots per year (allocation). Iโm counting the number of instances his email appears in each type. When he meets his allocation, I no longer want him to be able to see these time slots. Iโm trying to use the slice to show only open time slots (no owner email in the [booked by] column. And only show the type of time slots he is eligible to book (hasnโt met allocation).
This formula only returns the โelseโ in the first if expression even though all other values should be True.
I just realized that my logic is definitely offโฆeach row is being evaluated on all 5 criteria. I think Iโm trying to do too much with slices
Try something like this:
AND(
ISBLANK( [Booked By] ) ,
SWITCH( [Sail Type] ,
"WEP" ,
LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWEP YTDโ)>=12
,
"WEA" ,
LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWEA YTDโ)>=12
...
TRUE
)
)
Thank you for trying. I feel like we are getting closer but this didnโt work. It only included the WEP rows when I set the WEP YTD value >=12. When I reset the value to less than 12 I get no results. Here is the expression I used:
AND(
ISBLANK( [Booked By] ) ,
SWITCH( [Sail Type] ,
โWEPโ, LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWEP YTDโ)>=12,
โWEAโ, LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWEA YTDโ)>=12,
โWAโ, LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWA YTDโ)>=24,
โWPโ, LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWP YTDโ)>=36,
โฆ
TRUE
)
)
Oh, I just copied your existing LOOKUP() formulas. Just flip the equalities around.
OK. You are genius!
I need to go back and reread SWITCH expression over and over.
If there is still time, I sent Santa a note to put you on the top of the nice list!
Thank you again! This is great! Happy Holidays!
You can rewrite it like this, and itโll perform the same, if that is easier for you to understand. Remember that all statements inside of AND must return a TRUE/FALSE value.
AND(
ISBLANK( [Booked By] ) ,
IFS(
[Sail Type] = โWEPโ, LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWEP YTDโ)<12,
[Sail Type] = โWEAโ, LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWEA YTDโ)<12,
[Sail Type] = โWAโ, LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWA YTDโ)<24,
[Sail Type] = โWPโ, LOOKUP(USEREMAIL(),โOwnersโ, โOwner Emailโ, โWP YTDโ)<36,
TRUE
)
)
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |