Grouping Values into Ranges

Options
SLam
SLam Member

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 ✓
    Options

    @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 ✓
    Options

    Domo is somehow not seeing my back ticks.

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

  • GrantSmith
    GrantSmith Coach
    edited July 11 Answer ✓
    Options

    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 ✓
    Options

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

    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 ✓
    Options

    Domo is somehow not seeing my back ticks.

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

  • GrantSmith
    GrantSmith Coach
    edited July 11 Answer ✓
    Options

    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!**