Subtract resulting values of CASE function

Stephanie Golly-Oracle

Summary

Need to subtract two values that are first derived using the CASE function

Content

I have created two measure columns that use a CASE function. I am trying to subtract the resulting values. I can get it to work when I subtract a constant such as the number 1 below:

(CASE  WHEN "Assessment Details"."Assessment Result Entry Start Date" = date '2019-05-01' AND "Assessment Result Details"."Activity Name" = 'Audit Test' THEN COUNT(DISTINCT "Control Details"."Control Name") END) - 1

But when I try to subtract the values that would result from two CASE statements nothing returns:

(CASE  WHEN "Assessment Details"."Assessment Result Entry Start Date" = date '2019-05-01' AND "Assessment Result Details"."Activity Name" = 'Audit Test' THEN COUNT(DISTINCT "Control Details"."Control Name") END) - (CASE  WHEN "Assessment Details"."Assessment Result Entry Start Date" = date '2019-05-03' AND "Assessment Result Details"."Activity Name" = 'Audit Test' THEN COUNT(DISTINCT "Control Details"."Control Name") END)

Thoughts?

Thanks!

Wendy Ware

Hi Stephanie, if one of the case statement results is null then the result of the subtraction will be null.  Have you looked at the results of both case statements as individual columns first so you can see what values they are returning?  Wendy

Stephanie Golly-Oracle

Good to know. In this case, yes, each returns a value. I attached an example of the report output. Variance is where I am expecting 5-2 to show 3 on the UK line.

 

RichardChan

Interesting. Initial thoughts

What is your aggregation set to

Can you create two separate calculated on the source then create a third logical column that then does the subtraction