I need a stacked bar chart where the user can select what fields are in it using a multi select box.
The issue is that when all options are selected, quicksight automatically changes the text in the selection box to say “All” instead of being formatted like “option1, option2, option3”
I tried this in my calculated field:
ifelse(
in(“option2”, ${FieldSelector}) OR in(“All”, ${FieldSelector}),
avg({option2}),
null
)
This should return the average aggregate of the field option2 if it finds “option2” OR “All” in my selection box, otherwise return null.
But it doesn’t handle the “All” case correctly (Also tried using “Select All”). Quicksight is doing something when all options are selected and I don’t know how to check for it. Without being able to do this check, there would be no way to have the bar chart show all options at once. It works fine otherwise, but as soon as you select every option, it automatically changes the text to “All” and the calculated field doesn’t pick up on it and determines that no options are selected at all.
The workaround I’m using is to add an option to the selection box called “Placeholder” that doesn’t do anything and there is no reason to select, so that the user won’t ever have all options selected. But that’s a pretty bad/confusing solution to such a simple problem.
Hi @NeilTheFrobot,
Have you tested out the calculated field without the additional ‘OR’ statement included? Technically if ‘All’ is selected, each value will be selected so you shouldn’t need to include that in your formula.
If that does not help the issue, could you share a couple screenshots that help illustrate how the field is being used in the visual so that we can gain a full picture of the scenario?
I originally wrote the calculated field without looking for the “All” and just looking for the name of the option in the selection box. But I saw that if you select all available options, it suddenly doesn’t recognize ANY of them being selected.
Here are some images.
I just want to let the user select what fields are in a stacked bar chart.
In the first image, all fields except one are selected and it works just as expected. It also works if you select any number of fields, as long as you don’t select all that are available.
In the second picture, every option is selected and now nothing shows up at all. It is no longer able to pick up on it and I believe this could have to do with how the text changes from “Roof Available, Yard Available… etc” to just saying “All”.
Hi @NeilTheFrobot, Apologies for missing your last response.
So to make sure I’ve got everything correct, so in your original post, instead of each bar have separate sections for each value selected, it groups the bar into one ‘All’ value instead?
To make sure I have the whole scenario; could you provide all the calculated fields you’re using in this and how are the being used in the visual (which section of the field well do you have each field in)?
Unless you’ve been able to figure out a work around in the interim?
No, so once all options are selected it suddenly does not show any data at all. None of the “if” statements get triggered -
”ifelse(
in(“Roof Available”, ${FieldSelector}),…”
If I have “Roof Available” selected in my drop down list (but not every option available selected) then this if statement will be true. But as soon as you select all options in the list, this statement no longer returns true, even though “Roof Available” is still checked.
You can show that something does change internally once all options are selected by how the text shown on the control changes from listing each name that is checked in the list to just displaying “All” once every option is selected.
Right now quick suite is down and I can’t access anything, but once it is up again I could probably make a minimal and easily reproducible version of this. I’m almost positive it’s a general phenomenon and would have the same issue even with other visuals or calculated fields.
