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

Thank you for the revised formula!
0