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)
Best Answer
-
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.
0
Answers
-
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.
0 -
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'
END0 -
"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.
1 -
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)0 -
Thanks dthierjung
I truly appreciate it!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive