"Select All" problem in parameter for calculated field

Question & Answer
, , , ,
1

I have encountered two issues related to a multi-value parameter called “Filter”. The parameter includes values corresponding to categories CAT_A through CAT_H. I aim to create a custom calculation field for setting up targets. When I select “Select All” in the filter parameter, I want the custom target to exclude targets from CAT_F and CAT_G.

The first issue arises when I set the “Filter” parameter default to “Select All”, but the target doesn’t function properly initially. I have to select “Select All” in the filter twice for the target to work as intended.

The second issue is that the targets for CAT_F and CAT_G do not appear in the chart. I seek guidance to address this problem or explore alternative options to achieve the desired output.

Thank you for your assistance.

Here’s the code for my calculated field :
ifelse(
notIn(‘ALL_VALUES’, ${Filter}), sumIf({target}, type <> ‘CAT_F’ AND type <> ‘CAT_G’)
,in(‘CAT_A’, ${Filter}), sumIf({target}, type = ‘CAT_A’)
,in(‘CAT_B’, ${Filter}), sumIf({target}, type = ‘CAT_B’)
,in(‘CAT_C’, ${Filter}), sumIf({target}, type = ‘CAT_C’)
,in(‘CAT_D’, ${Filter}), sumIf({target}, type = ‘CAT_D’)
,in(‘CAT_E’, ${Filter}), sumIf({target}, type = ‘CAT_E’)
,in(‘CAT_F’, ${Filter}), sumIf({target}, type = ‘CAT_F’)
,in(‘CAT_G’, ${Filter}), sumIf({target}, type = ‘CAT_G’)
,in(‘CAT_H’, ${Filter}), sumIf({target}, type = ‘CAT_H’)
)

2

Hello @olddog , welcome to the Quicksight community!

Could you breakdown what you mean here? Are you having to actually go into the filter and manually reselect the option and then you have the desired outcome?

Also, I’m not sure I understand the desired outcome, you want the filter to only show values for CAT_H through CAT_H, and exclude F and G but you still included CAT_F and CAT_G in your conditional statement.

1 Like
3

Hi @olddog,

The reserved value ‘ALL_VALUES’ is used for internal purposes only currently and is not available for cross checking in calculations.

The parameter can have one of two values in the ‘select all’ state.
When analysis/dashboard is loaded, the parameter is set with the default value as configured.
If that default value happens to include all the options in the parameter control, the ‘select all’ option there in (if set to be visible) will show up in checked state.
Then, as you change the selections and eventually return to ‘select all’ state (either by selecting the ‘select all’ explicitly or by selecting all the individual options), the parameter gets a NULL value.

So, when looking for ‘select all’ state from calculations, you should look for the full value set (if that is being set as default) as well as NULL value.
Like wise, when checking multi select parameters to see if a specific value is selected, you should check for that specific value as well as NULL value (to cover ‘select all’ state)

For multi value parameters, you can do the NULL check using
in(NULL, ${MultiValParam})
and for single value parameters, you can do it using
isNull(${SingleValParam})

I know this is not intuitive and have requested for a doc update to make this more discoverable.
This should help you fix your calculation and hence I’m marking this as solution for now.
If you still face issues, please post a sample using Arena and we can have another look at your specific calculation.

Regards,
Arun Santhosh
Pr QuickSight SA

2 Likes
4

Hi @ArunSanthosh, I actually have “NULL” values in my field that I need to keep because they have specific meaning for my visualizations. I’ve tried to set custom value for the “reserved value” of my parameter to denote “Select all” and then tried to use this custom value in my calculated field (for testing I just set it to “HASTALAVISTA” knowing it’s never going to appear in my field values) to the effect of: in(‘HASTALAVISTA’, ${MultiValParam}) to check if “Select all” is active - but it does not work.
Do you have any advice on what to do in this case, if I can’t check for NULL to denote “Select all”?

1 Like
5

Hi @jocelynz ,

As mentioned in my above post, the reserved value that you set for parameters under advanced settings can’t be used in your calcs to check parameter state. You can try changing the actual field value to replace NULL with some other string. [You can use coalesce function for this].

Regards,
Arun Santhosh
Pr QuickSight SA

1 Like