Grouping Values into Ranges

SLam
SLam Member
in Beast Mode

Hello, I am trying to group values into ranges in DOMO, but can't seem to get my beast mode to work. Am I doing something wrong?

I am trying 2 different methods:

1.

case

WHEN `Sale Liability` >= 50000000 AND <= 100000000 then '$50M-$100M'

WHEN `Sale Liability` >= 100000000 AND <= 250000000 then '$100M-$250M'

when `Sale Liability` >= 250000000 AND <= 500000000 then '$250M-$500M'

when `Sale Liability` > 500000000 then '$500M+'

else 0 end

2.

case

WHEN `Sale Liability` BETWEEN 50000000 AND 100000000 then '$50M-$100M'

WHEN `Sale Liability` BETWEEN 100000000 AND 250000000 then '$100M-$250M'

when `Sale Liability` BETWEEN 250000000 AND 500000000 then '$250M-$500M'

when `Sale Liability` > 500000000 then '$500M+'

else 0 end

Any help would be greatly appreciated!

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @SLam

    Method 1 is closest to what you would need for beast mode. The only change you need to repeat Sale Liability again after the AND. 

    case
WHEN `Sale Liability` >= 50000000 AND `Sale Liability` <= 100000000 then '$50M-$100M'
WHEN `Sale Liability` >= 100000000 AND `Sale Liability` <= 250000000 then '$100M-$250M'
when `Sale Liability` >= 250000000 AND `Sale Liability` <= 500000000 then '$250M-$500M'
when `Sale Liability` > 500000000 then '$500M+'
else 0 end
  • ArborRose
    ArborRose Coach
    Answer ✓

    Domo is somehow not seeing my back ticks.

    image.png

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • GrantSmith
    GrantSmith Coach
    edited July 2024 Answer ✓

    Alternatively, because CASE statements will exit on the first condition that's true, you can go in reverse order to simplify your beast mode as the prior conditions are implied as false.

    CASE 
WHEN `Sale Liability` >= 500000000 THEN '$500M+'
WHEN `Sale Liability` >= 250000000 THEN '$250M-$500M'
WHEN `Sale Liability` >= 100000000 THEN '$100M-250M'
WHEN `Sale Liability` >= 50000000 THEN '$50M-100M'
ELSE 'Below $50M'
END
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @SLam

    Method 1 is closest to what you would need for beast mode. The only change you need to repeat Sale Liability again after the AND. 

    case
WHEN `Sale Liability` >= 50000000 AND `Sale Liability` <= 100000000 then '$50M-$100M'
WHEN `Sale Liability` >= 100000000 AND `Sale Liability` <= 250000000 then '$100M-$250M'
when `Sale Liability` >= 250000000 AND `Sale Liability` <= 500000000 then '$250M-$500M'
when `Sale Liability` > 500000000 then '$500M+'
else 0 end
  • ArborRose
    ArborRose Coach

    Make sure you have your conditions exculsive. 

    CASE
    
WHEN Sale Liability >= 50000000 AND Sale Liability < 100000000 THEN '$50M-$100M'
    
WHEN Sale Liability >= 100000000 AND Sale Liability < 250000000 THEN '$100M-$250M'
    
WHEN Sale Liability >= 250000000 AND Sale Liability < 500000000 THEN '$250M-$500M'
    
WHEN Sale Liability >= 500000000 THEN '$500M+'
    
ELSE 'Below $50M'
    
END

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ArborRose
    ArborRose Coach
    Answer ✓

    Domo is somehow not seeing my back ticks.

    image.png

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • GrantSmith
    GrantSmith Coach
    edited July 2024 Answer ✓

    Alternatively, because CASE statements will exit on the first condition that's true, you can go in reverse order to simplify your beast mode as the prior conditions are implied as false.

    CASE 
WHEN `Sale Liability` >= 500000000 THEN '$500M+'
WHEN `Sale Liability` >= 250000000 THEN '$250M-$500M'
WHEN `Sale Liability` >= 100000000 THEN '$100M-250M'
WHEN `Sale Liability` >= 50000000 THEN '$50M-100M'
ELSE 'Below $50M'
END
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories