WOW comp calculation not correct

Anna_Otake
Anna_Otake Contributor
in Beast Mode

Hello, I am looking at this Domo Card and the WOW Comp calculation looks incorrect.

Last Week Spend is $0 and Spend 2 weeks ago is also $0 but WOW is not 0%. Does anyone have any idea why this can be the case?

image.png

For reference:

LW Spend calculation:

sum((CASE WHEN ((FY Order = 0) AND (Fiscal Week No = (Current Week - 1))) THEN Amount_Cost END))

2 Weeks Ago Spend calculation:
sum((CASE WHEN ((FY Order = 0) AND (Fiscal Week No = (Current Week - 2))) THEN Amount_Cost END))

WOW Spend calculation:

((sum(case when Fiscal Week Index=(Current Week Index-1) then Amount_Cost end))

(sum(case when Fiscal Week Index=(Current Week Index-2) then Amount_Cost end)))
/
(sum(case when Fiscal Week Index=(Current Week Index-2) then Amount_Cost end))

Tagged:

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited June 2023 Answer ✓

    Hmm odd. 0/0 should return a null value not what you are seeing. Is there a reason you don't use the same logic from the WOW % as you do in the spend LW and spend 2LW columns? For example for Spend LW why don't you use:
    SUM(CASE WHEN Fiscal Week Index = Current Week Index - 1 THEN Amount_Cost END)

    I'm thinking perhaps the issue is not with the WOW calc, but instead the other 2 calcs.

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited June 2023

    It looks like you are missing a minus sign in your beastmode. Additionally, change % calculation is: (New Value - Initial Value) / Initial Value. So I would write your WOW spend calculation as follows:

    (SUM(CASE WHEN Fiscal Week Index = Current Week Index - 1 THEN Amount_Cost END)

    -

    (SUM(CASE WHEN Fiscal Week Index = Current Week Index - 2 THEN Amount_Cost END)))

    /

    (SUM(CASE WHEN Fiscal Week Index = Current Week Index - 2 THEN Amount_Cost END))

    If I solved your problem, please select "yes" above

  • Anna_Otake
    Anna_Otake Contributor

    Thanks @colemenwilson for your input!
    Totally my bad, I forgot to put the minus sign in the above description!

    I have this as my calculation now and it's still not working.

    ((sum(case when Fiscal Week Index=(Current Week Index-1) then Amount_Cost end))

    (sum(case when Fiscal Week Index=(Current Week Index-2) then Amount_Cost end)))
    /
    (sum(case when Fiscal Week Index=(Current Week Index-2) then Amount_Cost end))

  • ColemenWilson
    ColemenWilson Coach
    edited June 2023

    Hi Anna,

    It still looks like the minus sign is missing. Along with extra parentheses.

    If I solved your problem, please select "yes" above

  • ColemenWilson
    ColemenWilson Coach

    Can you try copying and pasting my beastmode into your card and seeing if that works?

    If I solved your problem, please select "yes" above

  • Anna_Otake
    Anna_Otake Contributor

    @colemenwilson hmm weird! When I copy and paste it into this comment section, the minus sign goes away.
    But this is what I have which is the same equation that you provided for me. I copy and pasted yours but nothing changed.

    image.png

  • ColemenWilson
    ColemenWilson Coach
    edited June 2023 Answer ✓

    Hmm odd. 0/0 should return a null value not what you are seeing. Is there a reason you don't use the same logic from the WOW % as you do in the spend LW and spend 2LW columns? For example for Spend LW why don't you use:
    SUM(CASE WHEN Fiscal Week Index = Current Week Index - 1 THEN Amount_Cost END)

    I'm thinking perhaps the issue is not with the WOW calc, but instead the other 2 calcs.

    If I solved your problem, please select "yes" above

  • Anna_Otake
    Anna_Otake Contributor

    Great observation @colemenwilson!

    Thank you for pointing that out, that was the issue!

  • ColemenWilson
    ColemenWilson Coach

    @Anna_Otake Awesome, glad I could help! I'd appreciate if you marked my solution as the answer :)

    If I solved your problem, please select "yes" above

Categories