Filter calculated field for Tier 1 & 2 and All Tiers

Q&A
, ,
1

Hello All,

I’m trying to create a filter in my Quick Sight dashboard for a field called “Tier”, which contains the values “Tier 1”, “Tier 2”, “Tier 3”, “Tier 4”, and NULL.

I want to create a calculated field that gives me only two options to use in a filter:

  • “Tier 1 and Tier 2”: This option should represent a selection of just “Tier 1” and “Tier 2”.

  • “All Tiers”: This option should include all possible values: “Tier 1”, “Tier 2”, “Tier 3”, “Tier 4”, and NULL.

Could you please suggest a Quick Sight calculated field solution to achieve this?

Thanks in advance for your help!

-Rajesh

2

Hi @Rajesh1 and welcome to the Quick Sight community!

Start off by building a parameter with custom values, for this example we’ll called ‘Tiers’, with custom values:
Tier 1 and Tier 2
All Tiers

Next, create an ifelse calculated field to handle this parameter, something like:
ifelse(
${Tiers} = ‘All Tiers’, 1,
${Tiers} = ‘Tier 1 and Tier 2’ AND ({Tier} = ‘Tier 1’ OR {Tier} = ‘Tier 2’, 1,
0
)

Last step, add a filter to your visual(s) for the above calculated field; for filter condition, use ‘equals’ and for 'enter value, put ‘1’:

image
image184×358 10.5 KB

It’s a different dataset structure but I built an example in Quick Sight Arena to show this process. Feel free to check out below:
Filter calculated field for Tier 1 & 2 and All Tiers

Let me know if you have any additional questions

2 Likes
3

Thank you so much Brett, it is now working as expected.

I would like to understand much more about this parameters and filters.

1 Like
4

Hi @Rajesh1,

I’m glad this worked for your scenario!
So when you build the parameter, it’s essentially creating values that you can use to handle scenarios, like we are doing in our ifelse calculation.
So in the calculated field, we’re essentially saying, if the parameter selection = ‘All tiers’, assign a 1 to each row. The second line says that if the parameter = ‘Tier 1 and Tier 2’ as well as the field ‘Tier’ = either ‘Tier 1’ or ‘Tier 2’, assign a 1.
So in my example, I left the ifelse field in the table to show the work being done; in the below screenshot, the parameter reads ‘AMER & APJ’ (like your ‘Tier 1 and Tier 2’), therefore the row with ‘AMEA’ has a ‘0’ instead of a ‘1’.

image
image350×310 5.67 KB

So then when I apply the filter to only show the value ‘1’, it hides the EMEA row from the visual.

Let me know if there’s any additional explanation you’d like or if this is what you were looking for

1 Like
5

Thank you for clear explanation Brett, understood now.

1 Like