Multiple Nested Case Statements Help

user16102
user16102 Member
in Charting

I'm trying to nest multiple conditional clauses together and having trouble. Based on the campaign_name, I want a specific calculation completed and value output of 'Good' or 'Bad.' 

 

This formula works properly when only specifying one campaign_name: 

CASE WHEN
SUM(case when `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then 1
else 0
end)
/
COUNT(`contact_id`)

<= 0.07

THEN 'Good'
ELSE 'Bad'
END

 

When I add a second conditional statement, it appears as if the second statement overwrites the first:

 

CASE WHEN

SUM(case when `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then 1
else 0
end)
/
COUNT(`contact_id`)

<= 0.07

THEN 'Good'

WHEN
SUM(case when `campaign_name` != 'VALUE1' AND `Abandon_Time` > 15 then 1
else 0
end)
/
COUNT(`contact_id`)

<= 0.05

THEN 'Good'
ELSE 'Bad'
END

 

Best Answer

Answers

  • guitarhero23
    guitarhero23 Contributor

    I don't have time at the moment to create a test dataset but let me see if I can get you further along.

     

    A full case statement is made up of

     

    (CASE

    WHEN X THEN Y

    ELSE Z

    END)

     

    you have multiple END statements, which would end a case statemement, but you only have one actual "CASE" function listed. You might have to try to utilize additional case statements or remove the extra END statements.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • user16102
    user16102 Member

    Thank you! I could be missing something but believe I have two additional CASE statements. I color-coded below:

     

    CASE WHEN

    SUM(case when `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then 1
    else 0
    end)
    /
    COUNT(`contact_id`)

    <= 0.07

    THEN 'Good'

    WHEN
    SUM(case when `campaign_name` != 'VALUE1' AND `Abandon_Time` > 15 then 1
    else 0
    end)
    /
    COUNT(`contact_id`)

    <= 0.05

    THEN 'Good'
    ELSE 'Bad'
    END

  • ST_-Superman-_
    ST_-Superman-_ Coach

    While looking at this case statement, I do have a few questions.  

     

    Are you wanting to use the same denominator for each campaign?  It looks like you are counting the number of times there was an abandon time greater then 15 for campaign "VALUE1" and dividing it by the total count of contact_id's in your dataset.  In other words, lets say you have a total count of 100 contact ids.  Only 50 of which were related to the value1 campaign.  If 4 show an abandon time of >15 then would you expect your result to be 4/50 or .08 ('Bad') or 4/100 or .04 ('Good')?

     

    I would also want to understand why you aren't using COUNT(DISTINCT `contact_id`)

     

     

     

    Apart from that, I think that your issue with the original case statement has to do with the ELSE 0 parts of your nested case statements.  This means that both of your nested case statements are able to be evaluated regardless of campaign name.  

     

    Maybe something like this would work?

    CASE WHEN
    (COUNT(CASE 
    WHEN `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then `contact_id`
    end)
    /
    COUNT(`contact_id`))
    <= 0.07
    THEN 'Good'

    WHEN
    (COUNT(CASE
    WHEN `campaign_name` != 'VALUE1' AND `Abandon_Time` > 15 then `contact_id`
    end)
    /
    COUNT(`contact_id`))
    <= 0.05
    THEN 'Good'
    ELSE 'Bad'
    END

     

    I think a sample dataset would help if this doesn't work.

     

    Thanks


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user16102
    user16102 Member

    @ST_-Superman-_  Thank you for the help!! You are correct in that I'm intending to only calculate against the contact ids related to the value1 campaign; so the result would be 4/50. And I didn't use count distinct because every contact id should already be discinct. 

     

    I tried the formula you suggested, and the results appear to provide the same output as mine. For whatever reason, the result defaults to 'Good' no matter what value I set in the comparison values of 0.05/0.07. 

     

    If I remove half of the statement and use just the below, it seemingly applies the calculation to the specified campaign. All other campaigns default to 'Good' though which may be part of the problem. 

     

    CASE WHEN
    (COUNT(CASE 
    WHEN `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then `contact_id`
    end)
    /
    COUNT(`contact_id`))
    <= 0.05

    THEN 'Good'
    ELSE 'Bad'
    END

    I added a sample sheet and changed 'VALUE1' to 'Customer1', 'Customer 2', and 'Customer 3'. 

    Thanks again!

  • user16102
    user16102 Member
    Answer ✓

    Attached

  • ST_-Superman-_
    ST_-Superman-_ Coach

    Give this a shot:

    case when 
    	(COUNT(case when `Abandon_Time`>15 then `contact_id` end) / COUNT(`contact_id`))<.05 then 'Good'
        when
        (COUNT(case when `Abandon_Time`>15 then `contact_id` end) / COUNT(`contact_id`))<.07 and `campaign_name`='Customer1' then 'Good'
        else 'Bad'
        end

     

    I used this for the "Abandon Rate"

    (COUNT(case when `Abandon_Time`>15 then `contact_id` end) / COUNT(`contact_id`))

    when you show this in a table and use the campaign_name field as a column, then it will separate out all of the data by campaign name for you.  Then I just used the performance calculation to classify each abandon rate as good or bad.1.png

     

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user16102
    user16102 Member

    Thanks again @ST_-Superman-_ . Judging by the formula working in the sample set provided, it would appear that my actual data set must be the problem. The same type of problem is still occurring - the first comparison clause is controlling 'Customer1' as well and modifying the clause that filters on campaign_name makes no change. I think I'll need to look at changing my data feed..

  • user056243
    user056243 Member

    Similar  issue

    but solution doesnot work

     

    CASE WHEN `Case Owner Manager` IN ('a', 'b,'c')
    THEN 'Tier 1'
    Else CASE WHEN `Case Owner Manager` IN ('d','e','f','g')
    Then 'Tier 2'
    Else
    CASE WHEN `Case Owner Manager` IN ('h', 'i')
    Then 'Strategic'
    ELSE 'Others'
    END

     

    Tried without else 

    CASE WHEN `Case Owner Manager` IN ('a', 'b,'c')
    THEN 'Tier 1'
    CASE WHEN `Case Owner Manager` IN ('d','e','f','g')
    Then 'Tier 2'
    CASE WHEN `Case Owner Manager` IN ('h', 'i')
    Then 'Strategic'
    ELSE 'Others'
    END

     

    still didnot work..

    ABle to run each condition separtely but not as nested

  • Valiant
    Valiant Coach

    @user056243 It looks like your syntax may be a bit off.


    See if this does what you're looking for:

    CASE WHEN `Case Owner Manager` IN ('a', 'b,'c')
               THEN 'Tier 1'
               WHEN `Case Owner Manager` IN ('d','e','f','g')
               Then 'Tier 2'
               WHEN `Case Owner Manager` IN ('h', 'i')
               Then 'Strategic'
               ELSE 'Others'
    END

    Sincerely,
    Valiant

  • ST_-Superman-_
    ST_-Superman-_ Coach

    @Valiant is on the right track.

     

    The way that a case statement evaluates is in the sequence of the when statements.

     

    In your example, 

    CASE 
        WHEN `Case Owner Manager` IN ('a', 'b,'c')
        THEN 'Tier 1'
    CASE 
        WHEN `Case Owner Manager` IN ('d','e','f','g')
        Then 'Tier 2'
    CASE 
        WHEN `Case Owner Manager` IN ('h', 'i')
        Then 'Strategic'
    ELSE 'Others'
    END

    You are using too many CASE statements.  Also, if you do run into a situation where you need to nest a case statement, each case statement must have at least one "when" statement, one "then" statement, and an "END" statement.  In your code, you have three case statements but you only "end" the last one.

     

    However, this is not a situation that requires nesting.

    CASE WHEN `Case Owner Manager` IN ('a', 'b,'c')
               THEN 'Tier 1'
               WHEN `Case Owner Manager` IN ('d','e','f','g')
               Then 'Tier 2'
               WHEN `Case Owner Manager` IN ('h', 'i')
               Then 'Strategic'
               ELSE 'Others'
    END

    Looking at @Valiant 's code, this is how Domo will evaluate it... (there is a typo in the first line) It should be "CASE WHEN `Case Owner Manager` IN ('a', 'b', 'c')... he put the comma in the wrong spot for b

     

    The first when statement - If the field `Case Owner Manager` is equal to 'a', 'b', or 'c' then it will output `Tier 1`

     

    If the first statement is not satisfied, it will then look to evaluate the second when statement - 

    if the field `Case Owner Manager` is equal to 'd','e', 'f', 'g' then it will output 'Tier 2'

     

    This is still part of the same case statement.  If the line of code being evaluated meets the second when condition, then the remaining when conditions are not evaluated at all.  This is why, for performance issues, you should list the most commonly occuring when conditions at the start of a long case statement.

     

    Hope this was helpful.  Let us know if you are still having issues with this case statement. 


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user056243
    user056243 Member

    Thank you. It worked.

     

    I have one more question can we use two different conditions for the same output.

    Example:

    CASE WHEN `Case Owner Manager` IN ('a', 'b,'c')
    THEN 'Tier 1'
    WHEN `Case Owner Manager` IN ('d','e','f','g')
    Then 'Tier 2'
    WHEN `Case Owner Manager` IN ('h', 'i')
    Then 'Strategic'
    ELSE 'Others'
    END

     

    also 'Case Owner' as one more condition here?

  • user11571
    user11571 Member

    I'm facing a similar problem to what I have read in this thread. I am trying to create a beast mode to measure ROI for multiple channels. When I set them up individually, they work just fine. But I am having trouble getting them into one Beast Mode. I need them to be in one variable for use in a chart. Here is my distinct syntax where I tried grouping them using '+'. This produces no results. I also got it to a point where it produced the wrong results. I also tried the solution in this thread of putting CASE WHEN in front of the whole thing, WHEN in front of the rest and END at the end of the whole thing. That resulted in the beast mode being invalid.  Any assistance is greatly appreciated!

     

    (SUM(CASE WHEN `Referred By Value Channel` = 'Digital' THEN `Rescard Reservation Total Commission` ELSE 0 END) - AVG(`Digital Mktg Spend`)) / AVG(`Digital Mktg Spend`)

    +

    (SUM(CASE WHEN `Referred By Value Channel` = 'Direct Mail' THEN `Rescard Reservation Total Commission` ELSE 0 END) - AVG(`Direct Mail Mktg Spend`)) / AVG(`Direct Mail Mktg Spend`)

    +

    (SUM(CASE WHEN `Referred By Value Channel` = 'Email' THEN `Rescard Reservation Total Commission` ELSE 0 END) - AVG(`Email Mktg Spend`)) / AVG(`Email Mktg Spend`)

    +

    (SUM(CASE WHEN `Referred By Value Channel` = 'Google PPC' THEN `Rescard Reservation Total Commission` ELSE 0 END) - AVG(`Google PPC Mktg Spend`)) / AVG(`Google PPC Mktg Spend`)

    +

    (SUM(CASE WHEN `Referred By Value Channel` = 'Radio' THEN `Rescard Reservation Total Commission` ELSE 0 END) - AVG(`Radio Mktg Spend`)) / AVG(`Radio Mktg Spend`)

    +

    (SUM(CASE WHEN `Referred By Value Channel` = 'Social' THEN `Rescard Reservation Total Commission` ELSE 0 END) - AVG(`Social Mktg Spend`)) / AVG(`Social Mktg Spend`)

    +

    (SUM(CASE WHEN `Referred By Value Channel` = 'Television' THEN `Rescard Reservation Total Commission` ELSE 0 END) - AVG(`TV Mktg Spend`)) / AVG(`TV Mktg Spend`)

This discussion has been closed.

Categories