Hello Appsheet Community,
I am setting up a reporting page dashboard. The user is able to quick edit a detail view of a filter form that automatically changes the slice of the data. Itโs based on this foundation: https://www.appsheet.com/samples/Allow-the-user-to-filter-a-view-based-on-a-form?appGuidString=91266...
For my project, I have 3 columns the user can filter by: Office, District, and HS. I was able to set it up that the tables will automatically filter the combination of what they select in the quick edit detail. To do that, I used this formula in the filter area of the slice:
AND(ANY(reportFilter[Office]) = [Office], ANY(reportFilter[HS]) = [hs], ANY(reportFilter[District]) = [district])
This works. But, I want the user to also be able to not filter by one of these options. To achieve that, I tried adding an โAllโ option to each filter. I then adjusted the formula in the filter slice to the following:
AND(
IF(NOT([_THISROW].[Office]=โAllโ), ANY(reportFilter[Office]) = [Office], ISNOTBLANK([Office])),
IF(NOT([_THISROW].[hs]=โAllโ), ANY(reportFilter[HS]) = [hs], ISNOTBLANK([Office])),
IF(NOT([_THISROW].[district]=โAllโ), ANY(reportFilter[District]) = [district], ISNOTBLANK([Office]))
)
This does not work . If I have a non-โAllโ value selected for each filter option the graphs filter correctly. But if I select โAllโ for any of them then the charts will not show me anything.
Solved! Go to Solution.
Figured it out!
AND(
IF(IN(โAllโ,reportFilter[Office]),ISNOTBLANK([Office]),ANY(reportFilter[Office]) = [Office])
,
IF(IN(โAllโ,reportFilter[hs]),ISNOTBLANK([Office]),ANY(reportFilter[hs]) = [HS])
,
IF(IN(โAllโ,reportFilter[district]),ISNOTBLANK([District]),ANY(reportFilter[district]) = [District])
)
Figured it out!
AND(
IF(IN(โAllโ,reportFilter[Office]),ISNOTBLANK([Office]),ANY(reportFilter[Office]) = [Office])
,
IF(IN(โAllโ,reportFilter[hs]),ISNOTBLANK([Office]),ANY(reportFilter[hs]) = [HS])
,
IF(IN(โAllโ,reportFilter[district]),ISNOTBLANK([District]),ANY(reportFilter[district]) = [District])
)
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |