Skip to content

    • Beast Mode

    Beast Mode

    Nested Case Statement

    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)

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

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

      1. CASE 
      2. WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c` END) > 111 THEN 'TIER 4'
      3. WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 71 THEN 'TIER 3'
      4. WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 35 THEN 'TIER 2'
      5. ELSE 'TIER 1'
      6.  
      7. END)
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**

    Answers

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

      1. CASE 
      2. WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c` END) > 111 THEN 'TIER 4'
      3. WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 71 THEN 'TIER 3'
      4. WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 35 THEN 'TIER 2'
      5. ELSE 'TIER 1'
      6.  
      7. END)
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Member

      @GrantSmith Thank you for your help!!

    • 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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories