Oracle Transactional Business Intelligence

Products Banner

Subtract resulting values of CASE function

113
Views
3
Comments

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!

Comments