Percent/Absolute Change WoW as a Beast Mode Column
I am a cardbuilder for my organization and I am having a tough time getting a Week over Week beast mode calculation correct. Our calendars are set on an admin level so the fiscal week is Monday - Sunday. In the screenshot attached, I want to show the % change WoW for the metric "Clicks". I want it so it will automatically do a WoW change based on the time period I select. Right now my formula is only for the prior week.... my questions are
1) What exactly is wrong with my formula? the output (See screengrab) is not the correct change
2) How would I code it so it will always do a WoW change based on the time period I choose for the table? Right now it's just for the prior week
((SUM(CASE WHEN WEEK(`Date`) = WEEK(CURRENT_DATE()) THEN `Clicks` ELSE 0 END) - SUM(CASE WHEN WEEK(`Date`) = WEEK(CURRENT_DATE()) - 1 THEN `Clicks` ELSE 0 END))
NULLIF(SUM(CASE WHEN WEEK(`Date`) = WEEK(CURRENT_DATE()) - 1 THEN `Clicks` ELSE 0 END), 0))
The reason your % Change isn't working is because you have Date / Week on the axis.
If you put a Date on the axis, then your beast mode only has access to the data related to THAT date. So it's impossible to know what the value was for the preceding date, b/c it's not accessible. Imagine your data is an excel spreadsheet. You wouldn't be able to calculate percent from last week without referencing the data from last week, which would be on a different row! (accessing data on a different row is the part that isn't possible.)