Magic ETL

Magic ETL

Sum of Nested Beast Modes not coming up with Correct Total

I've got a Beast Mode that I'm creating that is trying to come up with the average value for the sum of 5 Beast Mode Calculations.

If I use the first 4 in the Beast Mode and divide by 4 I get the correct value but if I try and add the 5th Beast Mode and divide by 5 the value that is returned is not correct.

The average number for any of this should be a decimal value and when I add the 5th on I get a number like 4.5 etc instead of something like .97.

Here is the Beast Mode with all 5 Beast Mode Calculations, it is the last one that causes the issue ((Sum(`SatisfactionWithRepairScore`)
/
Count(`SatisfactionWithRepairAnswered`)*.10)

 

This is the full Beast Mode

((CASE WHEN Sum((CASE
When `KeptInformedAnswered`='True'
Then 1
Else 0
End)) = 0 THEN 0

ELSE
SUM((CASE
When `KeptInformedScore`='True'
Then 1
Else 0
End))
/
Sum((CASE
When `KeptInformedAnswered`='True'
Then 1
Else 0
End))
End
+
CASE WHEN Sum((CASE
When `ReadyWhenPromisedAnswered`='True'
Then 1
Else 0
End)) = 0 THEN 0

ELSE
SUM((CASE
When `ReadyWhenPromisedScore`='True'
Then 1
Else 0
End))
/
Sum((CASE
When `ReadyWhenPromisedAnswered`='True'
Then 1
Else 0
End))
End
+
(Sum(`SatisfactionWithCustServiceScore`)
/
Count(`SatisfactionWithCustServiceAnswered`))*.10)
+
(Sum(`NPS`)
/
Count(`NPSAnswered`))*.10)
+
(Sum(`SatisfactionWithRepairScore`)
/
Count(`SatisfactionWithRepairAnswered`)*.10)
/5

 

Any help would be appreciated, or if I'm taking the wrong approach I'm totally open to other suggestions.

Thanks

Randy

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

  • Domo Employee
    Answer ✓

    Hello Randyb,

     

    It looks like you are missing a set of parentheses, so only the last Beast Mode was being divided by 5. The rest were being summed to the last Beast Mode divided by 5 with the current format of your parentheses. Please try the following query and let me know how it goes:

     

    (((CASE WHEN Sum((CASE
    When `KeptInformedAnswered`='True'
    Then 1
    Else 0
    End)) = 0 THEN 0
    ELSE
    SUM((CASE
    When `KeptInformedScore`='True'
    Then 1
    Else 0
    End))
    /
    Sum((CASE
    When `KeptInformedAnswered`='True'
    Then 1
    Else 0
    End))
    End
    +
    CASE WHEN Sum((CASE
    When `ReadyWhenPromisedAnswered`='True'
    Then 1
    Else 0
    End)) = 0 THEN 0
    ELSE
    SUM((CASE
    When `ReadyWhenPromisedScore`='True'
    Then 1
    Else 0
    End))
    /
    Sum((CASE
    When `ReadyWhenPromisedAnswered`='True'
    Then 1
    Else 0
    End))
    End
    +
    (Sum(`SatisfactionWithCustServiceScore`)
    /
    Count(`SatisfactionWithCustServiceAnswered`))*.10)
    +
    (Sum(`NPS`)
    /
    Count(`NPSAnswered`))*.10)
    +
    (Sum(`SatisfactionWithRepairScore`)
    /
    Count(`SatisfactionWithRepairAnswered`)*.10))
    /5


    Darius Rose
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • Is anyone able to help out with this request?

  • Domo Employee
    Answer ✓

    Hello Randyb,

     

    It looks like you are missing a set of parentheses, so only the last Beast Mode was being divided by 5. The rest were being summed to the last Beast Mode divided by 5 with the current format of your parentheses. Please try the following query and let me know how it goes:

     

    (((CASE WHEN Sum((CASE
    When `KeptInformedAnswered`='True'
    Then 1
    Else 0
    End)) = 0 THEN 0
    ELSE
    SUM((CASE
    When `KeptInformedScore`='True'
    Then 1
    Else 0
    End))
    /
    Sum((CASE
    When `KeptInformedAnswered`='True'
    Then 1
    Else 0
    End))
    End
    +
    CASE WHEN Sum((CASE
    When `ReadyWhenPromisedAnswered`='True'
    Then 1
    Else 0
    End)) = 0 THEN 0
    ELSE
    SUM((CASE
    When `ReadyWhenPromisedScore`='True'
    Then 1
    Else 0
    End))
    /
    Sum((CASE
    When `ReadyWhenPromisedAnswered`='True'
    Then 1
    Else 0
    End))
    End
    +
    (Sum(`SatisfactionWithCustServiceScore`)
    /
    Count(`SatisfactionWithCustServiceAnswered`))*.10)
    +
    (Sum(`NPS`)
    /
    Count(`NPSAnswered`))*.10)
    +
    (Sum(`SatisfactionWithRepairScore`)
    /
    Count(`SatisfactionWithRepairAnswered`)*.10))
    /5


    Darius Rose
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Yes you are correct I figured that out late last night, thought I'd take another shot at it since I wasn't getting any response.

    Really appreciate your reply and reinforcement that I had corrected appropriately.

    Thanks

    Randy

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