Dynamic filters in dashboart ...Histogram that omits blank values ​​from an enum column

[Msant77s]

Hello dears, I am contacting you to ask you about an inconvenience I am having when developing my application! It turns out that one of the tables that I am trying to represent in a dashboar has columns of type enum with validations to pass or not to the next column (This is because it is an inspection form and in many cases depending on the response it passes or not the next). When developing the filter and the views associated with that filter, I can't find a way for the charts not to count the blank cells...example: My chart is left with YES, NO, BLANK. I detail the condition of the filter... I don't know if something can be added to this expression to help me with the columns that have this inconvenience.

AND(IF(ISBLANK(ANY(DashboardfilterPro [INDUSTRY])), true, ANY(DashboardfilterPro [INDUSTRY])=[INDUSTRY]),

IF(ISBLANK(ANY(DashboarfilterPro [Overall Machine or Equipment Risk Level])), true, ANY(DashboarfilterPro [Overall Machine or Equipment Risk Level])=[Overall Machine or Equipment Risk Level]),

IF(ISBLANK(ANY(DashboarfilterPro [Query Start])), true, ANY(DashboarfilterPro [Query Start])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [End of Query])), true, ANY(DashboarfilterPro [End of Query]) >=[Date]))

On other individual slices for each column I have tried this:

ISNOTBLANK([Type of emergency stop device:])

When I do it for each individual column it works... the issue is then being able to manage my dashboar with a single filter!!

From already thank you very much for your time!!

[Msant77s]

well, and if I already have a condition in those enum that is as follows: "[Does it have Emergency Stop?]="YES" "...can I add that other context condition?...how would the expression

[Msant77s]

Well, in the form that the user navigates I already use a show if to get those questions if the answer is "NO" to if it has an emergency stop.

[Msant77s]

If you want and it's not a bother, I'll share an extract of the table in excel file so you can see the panorama!

https://docs.google.com/spreadsheets/d/1WCkQhiwnB70ueFmYlWlckCR8jUkX2dqWzjhlpOQuTFM/edit?usp=sharing

[Msant77s]

AND(([¿Posee Parada de Emergencia?]="SI"),( CONTEXT("View")=" Graficos Protecciones 2",[¿Posee Parada de Emergencia?]="Si")) ?¿

[Suvrutt_Gurjar]

I think it can be 

IF(CONTEXT("View")=" Graficos Protecciones 2", 

     [¿Posee Parada de Emergencia?]="Si",

     TRUE

      )

[Msant77s]

Dear, it still doesn't work both with the individual expression and with the last one that considers the two validations!!

How would be the way to do this through different slices for each graph? could handle everything under the same filter?

[Suvrutt_Gurjar · Accepted solution]

I think your best option is creating two slices. Please evaluate at your end before creating as it is substantial work.  I have no more suggestions.

1. First Slice  for those enum columns that are dependent on  [¿Posee Parada de Emergencia?]="Si" condition. Include only those yes, no enum columns and other necessary columns that are part of filter expression in this slice.

The expression for this first slice can be 

AND(IF(ISBLANK(ANY(DashboarfilterPro [SECTOR])), true, ANY(DashboarfilterPro [SECTOR])=[SECTOR]),

IF(ISBLANK(ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])), true, ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])=[Nivel de Riesgo general de máquina o equipo]),

IF(ISBLANK(ANY(DashboarfilterPro [Inicio de Consulta])), true, ANY(DashboarfilterPro [Inicio de Consulta])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [Fin de consulta])), true, ANY(DashboarfilterPro [Fin de consulta]) >=[Date]),

[Posee Parada de Emergencia?]="Si"

)

Create those Yes, No charts based on this slice

2) Second slice for all other charts with the filter expression as below. This slice should not have enum columns but whatever other necessary columns to create whatever charts you need.

AND(IF(ISBLANK(ANY(DashboarfilterPro [SECTOR])), true, ANY(DashboarfilterPro [SECTOR])=[SECTOR]),

IF(ISBLANK(ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])), true, ANY(DashboarfilterPro [Nivel de Riesgo general de máquina o equipo])=[Nivel de Riesgo general de máquina o equipo]),

IF(ISBLANK(ANY(DashboarfilterPro [Inicio de Consulta])), true, ANY(DashboarfilterPro [Inicio de Consulta])<=[Date]),

IF(ISBLANK(ANY(DashboarfilterPro [Fin de consulta])), true, ANY(DashboarfilterPro [Fin de consulta]) >=[Date])

)

The user should have to set only one common filter. Your slices will be created based on that filter.

[Msant77s]

Dear, thank you very much for your help, with these two options I was able to achieve the desired view 😁🎉! I really appreciate your patience and time spent, solving this is a great help for my work! In Argentina we would say that you have won an Asado (Roasted meat) and a delicious beer.✔️

 

Now I have two queries to solve, which may be minor but I can't solve them:

‌

1) Now by clicking on the desired data from the data. It sends me to fast table view and not to the initial view that I had in my dashboard without filter!

A_View and data selection in dashboar without filter

B_View and selection of data with filter

I would need that in the new dashboard with filter I can get visualization A

2) Additionally I have observed a problem in the graphics of the dashboar opted with filters!

Filtering the data by column [Level of general risk of machine or equipment[

Example: I select "Moderate" filter condition

The chart works fine for the filtered data, but the chart's color settings are not respected.

The colors of the graph are "Acceptable" = Green, "Moderate" = Yellow and "Substantial" = Red.

Could it be because this column is a ref column with three "Text" values ​​that cannot be defined chart colors?...If so, how could I pass this column to a format that allows me to define the specific colors of each filter "Acceptable", "Moderate", "Substantial.???

[Msant77s]

Dear, I was able to solve the table fast view by generating a ref view of the cut table!! I only have to solve the colors of the graphics..clause 2 of the query

[Suvrutt_Gurjar]

Hi @Msant77s 

Nice to know that the dashboard filtering works the way you want. Yes, it was a bit complex requirement and thanks for your patience also in persisting through the progressive solution steps.

Regarding pie chart colors that is an inherent issue. Please explore if you are able to use workaround suggested in the post below.

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Pie-chart-colors-applied-to-COLOR-and-switch-ex...

[Msant77s]

Dear, I have the configuration as specified in the publications! But when selecting a specific filter condition it does not respect the colors!! Example: I select "Substantial" in the Dashboard filter and when there is only one stage left, the graph takes the initial color ... in this case Green. When green should represent only "Acceptable"

[Suvrutt_Gurjar]

Hi @Msant77s ,

I see that you have opened another post topic. Anyway since this is a new topic, I suggestr please close this long thread and please follow your new topic of Pie chart on that topic so that the community could contribute better. Someone else also can suggest a solution if you have a fresh topic.

[Msant77s]

Ok perfect, you're right! I'm still on that other query!! Thank you very much again for the help you have given me, great job!