Nested Case When?

Zel

Hello. I have this slicer and its beast mode below:

Here's what I want to achieve:

If Project Maya is selected, the column 'Action' should show only 'Disregard' results.

If any of the other 3 was selected, the column 'Action' show only 'Resolved' results.

Not sure if nested case when statement will work on this. Please help.

MichelleH

@Zel Start with this KB Article to point you in the right direction!

Aditya_Jain

@Zel
Can you do something like-

SUM(CASE
WHEN
Project = 'Maya'
THEN
(CASE WHEN Action= 'disregard' THEN 1 ELSE 0 END)
WHEN
Project IN ('US','International','Mendel') THEN
(CASE WHEN Action ='email resolved' THEN 1 ELSE 0 END)
ELSE
0
END)

This should work. Here Project could be variable control or column based on your requirement. Looks like it should be a variable control. and please change the column names according to your dataset.
Let us know if this does not work

MichelleH

@Zel It sounds like you will need to set up a variable control for Customer (instead of a filter card) so that you can add conditions to your case statement based on the selected value.

Zel

Hi @MichelleH , apologies, I haven't tried setting up a variable control. Can you please give me an idea how to do it? It will be much appreciated.

MichelleH

@Zel Start with this KB Article to point you in the right direction!

Zel

thank you @MichelleH 😊

Zel

@MichelleH reviewing the link that you sent now. Also, just want to clarify, based on the output that I want to achieve (see previous post in this thread), does variable control also work with showing output from columns?

Because the 'Action' column from the dataset contains like 20 or more different actions. So based from the slicer above:

If Project Maya is selected, the column 'Action' should show only 'Disregard' results.

If any of the other 3 was selected, the column 'Action' show only 'Resolved' results.

MichelleH

@Zel Can you please share some example data to demonstrate the behavior you want to see?

Zel

@MichelleH please see below:

If I selected Project Maya, the 2nd column should only count 'disregard' actions.

If I selected US, International or Mendel, the 2nd column should only count 'email resolved' actions.

Currently, the below image only counts 'email resolved' actions.

Aditya_Jain

@Zel
Can you do something like-

SUM(CASE
WHEN
Project = 'Maya'
THEN
(CASE WHEN Action= 'disregard' THEN 1 ELSE 0 END)
WHEN
Project IN ('US','International','Mendel') THEN
(CASE WHEN Action ='email resolved' THEN 1 ELSE 0 END)
ELSE
0
END)

This should work. Here Project could be variable control or column based on your requirement. Looks like it should be a variable control. and please change the column names according to your dataset.
Let us know if this does not work

Zel

Hi @Aditya_Jain how can I get the value from which was selected in the slicer and assign it as a variable control - Project?

The thing is, for the initial slicer, I had to do the mapping of the customers/chain and assign them as Project Maya, US, International and Mendel.

Then for the variable control, this will be used in several cards on the dashboard. Will I be able to get the value from the initial slicer (whichever was clicked there and perform what the variable control should do?)

Aditya_Jain

@Zel - We can not take a value from Slicer and assign it to Variable control, but a value selected from a variable control can be used within a beast mode.
Please see if this helps