Multiple Nested Case Statements Help

user16102

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

 

user16102

Attached

guitarhero23

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.

user16102

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-_

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

user16102

@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

Attached

ST_-Superman-_

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.

 

 

user16102

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

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

@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-_

@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. 

user056243

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

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`)