Magic ETL

Magic ETL

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)

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • 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

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

  • 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

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

  • 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)

  • Thanks dthierjung

    I truly appreciate it!

     

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In