Categories
- All Categories
- 76 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14.1K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 41 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Subtract resulting values of CASE function
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
-
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
0 -
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.
0 -
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
0