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
Based on your expression, I would expect a blank Filter[Description]
value to โbring ALL Description typesโ. Does it not?
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]))
)
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?
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))
Hi , check your elipses
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โ
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 โฆ
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
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |