Trying to calculate with multiple conditions in Beast mode

I have this created in excel and it works perfectly, unfortunately, translating it to a case statement is proving to be difficult.

here is the case statement I am having issues validating with

by the way, I have created a beast mode with the calculations already and it is working, unfortunately, with DOMO's limitations in calculating within beastmodes, I have to use the raw calculations itself instead of the beast mode I already created for the calculation = (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`

(CASE
WHEN `Planned Hours`=0 OR `Total Hours from SOW Breakdown`=0 THEN ''
WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`=0%
THEN 'Within the Planned Hours'
WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`>10%
AND (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`<=20%
THEN 'is within 20% of Planned Hours'
WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`>0%
AND (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`<=10%
THEN 'is within 10% of Planned Hours'
ELSE 'No'
END)

Find more posts tagged with

Accepted answers

dthierjung

Without the exact error you're receiving, it's tough to troubleshoot this. I'm guessing a syntax error though.

My first guess would be your equality statements where you say ">10%" for example. You'll need to convert that to a decimal like ">.1", and so for the others.

Try that and let us know how it works.

All comments

dthierjung

Without the exact error you're receiving, it's tough to troubleshoot this. I'm guessing a syntax error though.

My first guess would be your equality statements where you say ">10%" for example. You'll need to convert that to a decimal like ">.1", and so for the others.

Try that and let us know how it works.

WorldWarHulk

Thanks for the response.

yes. it's not getting validated.

it still didn't work changing the 10% to .1

am I missing something else?

(CASE
WHEN `Planned Hours`=0 OR `Total Hours from SOW Breakdown`=0 THEN ''
WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`.0
THEN 'Within the Planned Hours'
WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`>.1
AND (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`<=.2
THEN 'Within 20% of Planned Hours'
WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`>.0
AND (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`<=.1
THEN 'Within 10% of Planned Hours'
ELSE 'No'
END

dthierjung

"WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`.0" is the issue.

Change that to: WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours` > .0

Notice that added ">" towards the end.

WorldWarHulk

I missed an "=" sign

(CASE
WHEN `Planned Hours`=0 OR `Total Hours from SOW Breakdown`=0 THEN ''
WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`=0.0
THEN 'Within the Planned Hours'
WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`>0.1
AND (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`<=0.2
THEN 'Within 20% of Planned Hours'
WHEN (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`>0.0
AND (`Planned Hours`-`Total Hours from SOW Breakdown`)/`Planned Hours`<=0.1
THEN 'Within 10% of Planned Hours'
ELSE 'No'
END)

WorldWarHulk

Thanks dthierjung

I truly appreciate it!