Case When Statement Only Picking up the first When

[rvickersevotix]

I have a very simple case statement I'm writing and applying to a pivot table. 

My Row column is a list of types which in the below case statement is the [class I] and [class II].

case
when ([Total NumericQuantity],[class I]) > 0 then ([Max NumericQuantity])*([Max NumericAnswer],[class I q])
when ([Total NumericQuantity],[class II]) > 0 then ([Max NumericQuantity])*([Max NumericAnswer],[class II q])

else 0

end

However it only ever returns the value for the first case statement.

I have checked that the total numeric quantity is returning the correct values for each row and the max numeric answer attached.

I am expecting this:

to show: 658.3 and 5.37 in the Case 1 column respectivley. But it is applying the 1.45 value to both.

Am I doing something wrong with my case statement or is there potentially a bigger issue in how the data is hung together in the back end?

[Ravid_PaldiTeam]

Hi @rvickersevotix , 

The way that the logic of case-when statements works is that the code would go to each case statement one by one according to their order and check if the statement is true. 

Once it found a true statement - it would skip to the end and ignore the rest of the statements. 

In your case, the total of class1 is greater than 0 in both cases AND it's the first statement and that's why your statement decided to go with class 1 in both times. 

In order to help you further, can you explain in simple words what business logic you wish to implement and I'll do my best to assist in re-phrasing the statement. 

Cheers,

Ravid

[email protected] 

Paldi Solutions | Sisense Experts for Embedded Analytics Tools

[rvickersevotix]

Hi @Ravid_PaldiTeam 

Firstly thanks for taking the time to respond.

In simple terms Im trying to do the following.

The user enters a numeric value and a typing, For Example "3" and "Cars". 
In another Table we hold another Typing and Numeric value that relate to this in someway, for Example "Cars (diesel)" and a numeric value of "1.25".
Because of how our system is set up I have to manually link the two sets of data together to get a conversion rate.

So IF TYPE = CARS THEN CALCULATE 3 MULTIPLIED BY THE NUMERIC VALUE(1.25) LINKED TO CARS (DIESEL)

I only want to multiply the number the user enters by the numeric number in my other table based on what I filter it by.

Hopefully that makes sense.

[Ravid_PaldiTeam]

Hi @rvickersevotix 

Your use case is not fully clear to me. 

Could you please elaborate around what you mean by "The user enters a numeric value and a typing, For Example "3" and "Cars". "

More specifically:

- Which user? (viewer? designer?) 

- Where he is going to type a value? In the cube? Within an input in the dashboard? If so - which input? 

Even though I'm not fully understand the user case, from the sentance below it sounds like you'll need to do some JS to do what you're looking for as you wish apply logic that is baes on a user selection rather then the data model. 

"I only want to multiply the number the user enters by the numeric number in my other table based on what I filter it by."

If you wish, we can hop on a quick call to review and guide you on your direction or provide you with a quote to achieve what you are looking to do. 

Looking forward,
Ravid

[email protected] 

Paldi Solutions | Sisense Experts for Embedded Analytics Tools