Trying to calculate with multiple conditions in Beast mode

WorldWarHulk
WorldWarHulk Member
in Magic ETL

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

  • dthierjung
    dthierjung Contributor
    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.

Answers

  • dthierjung
    dthierjung Contributor
    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.

  • WorldWarHulk
    WorldWarHulk Member

    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
    dthierjung Contributor

    "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
    WorldWarHulk Member

    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
    WorldWarHulk Member

    Thanks dthierjung

    I truly appreciate it!

     

Categories