Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Subtract resulting values of CASE function

322
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

  • Wendy Ware
    Wendy Ware Rank 2 - Community Beginner

    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
    Stephanie Golly-Oracle Rank 1 - Community Starter

    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.

     

    OTBI Variance.jpg

  • RichardChan
    RichardChan Rank 6 - Analytics Lead

    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