Sum of Nested Beast Modes not coming up with Correct Total

Randyb
Randyb Member
in Magic ETL

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

Best Answer

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

  • Chronos
    Chronos Member

    Is anyone able to help out with this request?

  • Darius
    Darius 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"
  • Randyb
    Randyb Member

    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

Categories