Change row filter condition for a slice based on user input

[Fof_Webmaster]

I’m filtering a slice based on user input within a dashboard as follows:
AND(ANY(Filter[Observation Type]) = [Observation Type], ANY(Filter[Description]) = [Description], ANY(Filter[Work Status]) = [Work Status], ANY(Filter[Start Date]) <= [Date and time], ANY(Filter[End Date]) >= [Date])

This expression works fine. But I want the user to have a choice whether or not to filter on “Description”. He would choose Yes or No to “Do you want to filter on Description?”.
If he chooses NO the expression would bring ALL Description types. I believe I would need to put together the above expression with another expression that doesn’t filter on Description, based on an IF statement.

Can this be done in one row filter expression and if so how?

Thanks
Bob

[Steve]

Based on your expression, I would expect a blank Filter[Description] value to “bring ALL Description types”. Does it not?

[Fof_Webmaster]

Using Slices I’m trying to create a row filter condition (Yes/No). To avoid confusion with the AppSheet function “Filter” I changed the table name Filter to FilterTable.

This formula works:
AND(ANY(FilterTable[Observation Type]) = [Observation Type], ANY(FilterTable[Description]) = [Description], ANY(FilterTable[Work Status]) = [Work Status], ANY(FilterTable[Start Date]) <= [Date and time], ANY(FilterTable[End Date]) >= [Date])

The following formula doesn’t work. It gives the error - Unable to find column ‘FilterDesc’. This doesn’t make sense to me since FilterDesc is a column of FilterTable like the other columns in the formula.

IFS(
[FilterDesc] = “Yes”,
(AND(ANY(FilterTable[Observation Type]) = [Observation Type], ANY(FilterTable[Description]) = [Description], ANY(FilterTable[Work Status]) = [Work Status], ANY(FilterTable[Start Date]) <= [Date and time], ANY(FilterTable[End Date]) >= [Date])),

[FilterDesc] = “No”,
(AND(ANY(FilterTable[Observation Type]) = [Observation Type], ANY(FilterTable[Work Status]) = [Work Status], ANY(FilterTable[Start Date]) <= [Date and time], ANY(FilterTable[End Date]) >= [Date]))
)

[Tin_Nguyen1]

I’m trying to just filter for the past week. I’m not sure what expression I need to create that slice. Can you help?

[Steve]

Within the last 7 days:

([DateTime] > (NOW() - 7))

Since Sunday of this week:

([DateTime] > (EOWEEK(NOW()) - 7))

Since Saturday of this week:

([DateTime] > (EOWEEK(NOW()) - 8))

[Lynn]

Hi , check your elipses

[Fof_Webmaster]

Yes, I had unnecessary () before both AND, which was not needed. I eliminated them but that’s not the problem.

My error remains - Unable to find column ‘FilterDesc’

[pravse]

I think you need ANY(FilterTable[Filter_Desc]) = “Yes” (and ditto for No or just use IF with an else case).

Stylistically, I would consider writing this a bit differently. I’m assuming there is only one row in the FilterTable and its key is known. Let’s say the key is “1”. So you could have a hidden virtual column FIlterRef in the table you are filtering with a Ref to the FilterTable and the app formula “1”. Then you could just do a de-reference.

IF([FilterRef].[FilterDesc] = “Yes”,
AND([FilterRef].[ObservationType] = [Observation Type),
[FilterRef].[Description] = [Description],
…
)
… else case …

[Fof_Webmaster]

Thanks for your help Praveen. Your first recommendation works great. Now I have a REAL interactive dashboard - see attached browser pic. Everything is live and allows drill down. Query drop downs filter key parameters needed for land management. Query results are shown in a live map and live graph, both of which can be drilled down to a specific problem report. In this case I tapped a map location which opened a specific record within the query.

There are some opportunities which I’ll discuss with your support folk - The map and chart do not always update unless they are tapped. Can sync be turned off since - updates occur without syncing, syncs are slow (Premium plan) and they syncs build up with each change in query input? Requiring sync can cause 2 people working at the same time to swap queries - not good. The chart cannot be resized. Can the chart drill down be within this single view rather than opening new views?

Thanks again
Bob Haizmann