Solved: (How to ?) Slices with multiples row filter condit...

David_GOURIOU · 05-08-2020 06:22 AM

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

Jonathan_S

OR([Column] = "yes", [Column] = "its ok", [Column] = "for sure")

View solution in original post

Steve

([Column] = "no") would appear to be all you need based on your vague question.

David_GOURIOU

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 ?

Jonathan_S

OR([Column] = "yes", [Column] = "its ok", [Column] = "for sure")

David_GOURIOU

It’s ok !

Thanks all

Jonathan_S

No Problem, appsheet is great. you will see.

hru

Maybe:

	AND(
		[Column] = "yes",
		[Column] = "its ok",
		[Column] = "for sure"
	)

Steve

Another approach:

IN([Column], {"yes", "its ok", "for sure"})

Jonathan_S

Hey Steve, How would you take a sentence of words and split each word into list? This could be the solution to my question.

Steve

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..

Mike_T

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?

Mike_T

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”)
)
)

Marc_Dillon

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?

Mike_T

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”)

))

Marc_Dillon

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.

Mike_T

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

Marc_Dillon

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
    )
  )

Mike_T

Marc_Dillon:

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
)
)

Marc_Dillon

Oh, I just copied your existing LOOKUP() formulas. Just flip the equalities around.

Mike_T

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!

Marc_Dillon

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
)
)

(How to ?) Slices with multiples row filter condition