General OBIEE

Get Involved. Join the Conversation.

Topic

    Stephanie Golly
    Subtract resulting values of CASE function
    Topic posted May 3, 2019 by Stephanie Golly, tagged Analyses, OBIEE Answers, OTBI 
    160 Views, 3 Comments
    Title:
    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!

    Comment

     

    • 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

        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.

         

    • Richard Chan

      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