WOW comp calculation not correct

Anna_Otake

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?

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

ColemenWilson

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.

ColemenWilson

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

Anna_Otake

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

Hi Anna,

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

ColemenWilson

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

Anna_Otake

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

ColemenWilson

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.

Anna_Otake

Great observation @colemenwilson!

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

ColemenWilson

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