Advanced filter?

AppSheet AppSheet Q&A
1

Hi I have a table (“table”) that has various total columns - fed by a date select filter. the one below is the [FEE] column formula:

SUM(SELECT(table[FEE] , AND( [Date Submitted]>=ANY(Date Filter[Start Date]), [Date Submitted] <=ANY(Date Filter[End Date]))))

I am hoping to filter this further to exclude any that rows that are"FREE" in the column [Customer Type], this column is an ENUM List.

I am also using a slice for this:

IN( [Date Submitted], SELECT(table [Date Submitted], AND( [Date Submitted]>=ANY(Date Filter[Start Date]), [Date Submitted] <=ANY(Date Filter[End Date]))))

Im not sure if that would be the best place to add the solution?

Can anyone help please, Ive tried an IF statement but cant seem to get this to work

Thanks in advance

2

Maybe your slice filter could be something like below, assuming [Customer Type] is enumlist column.

IN( [Date Submitted], SELECT(table [Date Submitted], AND( NOT( IN ( “FREE”, [Customer Type] )), [Date Submitted]>=ANY(Date Filter[Start Date]), [Date Submitted] <=ANY(Date Filter[End Date]))))

1 Like
3

Hmmm - Ive overwritten the slice code to that code and it isnt filtering the FREE ones out, sorry.

Any other options please?

4 
AND(
  NOT(IN("FREE", [Customer Type])),
  IN(
    [Date Submitted],
    SELECT(
      table[Date Submitted],
      AND(
        ([Date Submitted] >= ANY(Date Filter[Start Date])),
        ([Date Submitted] <= ANY(Date Filter[End Date]))
      )
    )
  )
)
2 Likes
5

Thats brilliant Steve thanks very much!! If you get chance - can you guide me on my Pie chart problem in my other question please - tearing my hair out!!

Thanks again

6

I’m afraid I have virtually no experience with charts. Sorry.

1 Like
7

Thanks anyway buddy!!

1 Like