Hello,
I have a card that is used to display YoY growth per week for a market. My setup is as follows:
-A clustered bar chart with symbols
-Week # is the x axis
-LY & TY Dollars is the y axis
-YoY Growth % are the symbols which are connected to a secondary axis.
The chart looks great and is displaying everything correctly, however there is one issue. Due to shifts related to Covid, Amazon Prime Day took place in week 42 LY whereas this year it took place in week 26. For this reason, I am trying to create a beast mode which checks whether the current week is week 26, and then displays the % growth vs 42 LY so that I can display that alternative growth rate as a tooltip alongside the 26 TY vs 26 LY growth rate. My Beast Mode is as follows:
case when `Week` = 26 then
CONCAT(
(
SUM(case when `Week` = 26 then
case when `Year` = 2021 then `Dollars Total` else 0 end
else 0 end)
-
SUM(case when `Week` = 42 then
case when `Year` = 2020 then `Dollars Total` else 0 end
else 0 end)
)
/
SUM(case when `Week` = 42 then
case when `Year` = 2020 then `Dollars Total` else 0 end
else 0 end)
,' ','vs Prime LY')
else '' end
Below is the result:
-Note that the 39% is the true growth rate for week 26 YoY.
The issue I am running into is that the formula shows "Infinity" for week 26 on the chart because it is pulling a zero for week 42. Are there any functions in Beast Mode which enable you to pull in a # from a separate week in this instance other than the one which is related to the column it is set for? Any help is appreciated!
