Case When Statement Only Picking up the first When
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?