POP WOW %change Beast mode Error
Marketing Solutions | |||
2019 | 2020 | % Var | |
Wk 1 | 503 | 0 | -100% |
Wk 2 | 745 | 2 | -100% |
Wk 3 | 569 | 54 | -91% |
Wk 4 | 30 | 283 | 844% |
Help my beast mode is not working for the % variance. I keep getting error message or no result in my table
(
(SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE())
AND (WEEKOFYEAR(`Opp | Close Date *`)= WEEKOFYEAR(CURDATE()))
THEN SUM(CASE WHEN `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *`
ELSE 0 END)
ELSE 0
END)
- SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1
AND (WEEKOFYEAR(`Opp | Close Date *`)= WEEKOFYEAR(CURDATE()))
THEN SUM(CASE WHEN `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *`
ELSE 0 END)
ELSE 0
END)
)
/ NULLIF(SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1
AND (WEEKOFYEAR(`Opp | Close Date *`)= WEEKOFYEAR(CURDATE()))
THEN SUM(CASE WHEN `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *`
ELSE 0 END)
ELSE 0
END),0)
)
Best Answer
-
It looks like the issue is because you are trying to account for the week in your Beast Mode, when the rows in your table are already separated by week. You can actually use a much simpler calculation that only compares the year over year because each row of your table is already only includes data from the same week of the year. Try this:
( (SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) And `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *` ELSE 0 END) - (SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1 AND `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *` ELSE 0 END) ) / (SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1 AND `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *` ELSE 0 END)
1
Answers
-
It looks like the issue is because you are trying to account for the week in your Beast Mode, when the rows in your table are already separated by week. You can actually use a much simpler calculation that only compares the year over year because each row of your table is already only includes data from the same week of the year. Try this:
( (SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) And `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *` ELSE 0 END) - (SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1 AND `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *` ELSE 0 END) ) / (SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1 AND `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *` ELSE 0 END)
1 -
Thank you for the revised formula!
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 308 Workbench
- 7 Cloud Amplifier
- 10 Federated
- 3.8K Transform
- 660 Datasets
- 117 SQL DataFlows
- 2.2K Magic ETL
- 818 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 84 App Studio
- 46 Variables
- 781 Automate
- 191 Apps
- 483 APIs & Domo Developer
- 84 Workflows
- 23 Code Engine
- 43 AI and Machine Learning
- 22 AI Chat
- 3 AI Projects and Models
- 18 Jupyter Workspaces
- 408 Distribute
- 116 Domo Everywhere
- 281 Scheduled Reports
- 11 Software Integrations
- 146 Manage
- 142 Governance & Security
- 8 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 4.8K Archive