Nested Case Statement

kodonnellLT

I'm trying to count travel records that are completed and then put them into tiers. · 

     0-34 Tier 1

35-70 Tier 2

 71-110 Tier 3

   111+ Tier 4

 

 I keep getting an error using the beast mode below

 

CASE WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 35 THEN 'TIER 2'

WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 71 OR > 110 THEN 'TIER 3'

WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c` END) > 111 THEN 'TIER 4'

ELSE 'TIER 1'

END)

GrantSmith

You need to reverse the order of your SUM checks because if the sum is more than 111 it's already more than 35 so it will return Tier 2 first and quit as it executes the first case that matches. Check for Tier 4 then 3 then 2 then 1.

Also with your tier 3 check you only need to check for 71 because by logic all prior when statements are false.

CASE 
WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c` END) > 111 THEN 'TIER 4'
WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 71 THEN 'TIER 3'
WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 35 THEN 'TIER 2'
ELSE 'TIER 1'

END)

GrantSmith

You need to reverse the order of your SUM checks because if the sum is more than 111 it's already more than 35 so it will return Tier 2 first and quit as it executes the first case that matches. Check for Tier 4 then 3 then 2 then 1.

Also with your tier 3 check you only need to check for 71 because by logic all prior when statements are false.

CASE 
WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c` END) > 111 THEN 'TIER 4'
WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 71 THEN 'TIER 3'
WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 35 THEN 'TIER 2'
ELSE 'TIER 1'

END)

kodonnellLT

@GrantSmith Thank you for your help!!

kodonnellLT

Looks like I have to create this change in the Dataflow because

SELECT 

a.*

,COUNT(DISTINCT(case WHEN a."Status__c"='Booked' and (a."Status__c" = 'Booked' > 111 THEN 'TIER 4'))

FROM

"sfdc_travel_raw" a

I am getting an error