Percent/Absolute Change WoW as a Beast Mode Column

user32470

Hello, 

 

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))

jaeW_at_Onyx

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.)

 

this video https://www.youtube.com/watch?v=CDKNOmKClms&t=587s will show you how to accomplish your requirement.

 

 

Last thought. Be careful with your Week() function. The Week function will be a standard SQL implementation whose start of week is not necessarily related to your calendar week.  

I always recommend creating your own Date Dimension table where you explicitly declare the day of the week so you can avoid doing the calc in beast modes.