WOW comp calculation not correct
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))
Best 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 WHENFiscal Week Index
=Current Week Index
- 1 THENAmount_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
0
Answers
-
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 WHENFiscal Week Index
=Current Week Index
- 1 THENAmount_Cost
END)-
(SUM(CASE WHEN
Fiscal Week Index
=Current Week Index
- 2 THENAmount_Cost
END)))/
(SUM(CASE WHEN
Fiscal Week Index
=Current Week Index
- 2 THENAmount_Cost
END))If I solved your problem, please select "yes" above
0 -
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) thenAmount_Cost
end))(sum(case when
Fiscal Week Index
=(Current Week Index
-2) thenAmount_Cost
end)))
/
(sum(case whenFiscal Week Index
=(Current Week Index
-2) thenAmount_Cost
end))0 -
Hi Anna,
It still looks like the minus sign is missing. Along with extra parentheses.
If I solved your problem, please select "yes" above
0 -
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
0 -
@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.0 -
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 WHENFiscal Week Index
=Current Week Index
- 1 THENAmount_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
0 -
Great observation @colemenwilson!
Thank you for pointing that out, that was the issue!
0 -
@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
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive