Nested Case Statement

kodonnellLT
kodonnellLT Member
edited March 2023 in Beast Mode

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)

Best Answer

  • GrantSmith
    GrantSmith Coach
    edited January 2023 Answer ✓

    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    edited January 2023 Answer ✓

    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • kodonnellLT
    kodonnellLT Member

    @GrantSmith Thank you for your help!!

  • kodonnellLT
    kodonnellLT Member

    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

Categories