Grouping Values into Ranges

SLam

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!

MichelleH

@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

Domo is somehow not seeing my back ticks.

GrantSmith

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

MichelleH

@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

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

ArborRose

Domo is somehow not seeing my back ticks.

GrantSmith

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