In many cases, we want to know how our KPI’s did last week compared with the week before.
This forum post will show how to easily create a week over week chart comparison from current week.
The definition of “Week” can change from place to place.
While in some places the week starts on Monday and ends on Sunday, in other places the week starts on Sunday and ends on Saturday.
Modeling it properly will allow you to associate each day with the appropriate week and compare performance across weeks
Step 1 - Create WeekStartDate field
The following SQL expression produces the Week Start Date for a given date field (assuming the week starts on Monday). It is highly recommended to apply it on the date key within the dates dimension table.
CASE WHEN dayofweek(Date) = 1 THEN Date
WHEN dayofweek(Date) = 2 THEN adddays(Date,-1)
WHEN dayofweek(Date) = 3 THEN adddays(Date,-2)
WHEN dayofweek(Date) = 4 THEN adddays(Date,-3)
WHEN dayofweek(Date) = 5 THEN adddays(Date,-4)
WHEN dayofweek(Date) = 6 THEN adddays(Date,-5)
END AS WeekStartDate
Step 2 - Add Week Filter to the Dashboard
Create a new Dashboard, and add the “WeekStartAt” filter you've created in Step 1 to the dashboard's filters
Make sure, you’re using “Days” filter type, since this is the level of granularity which represents the week.
Then, select one week from the list:
Step 3 - Create Numeric Indicator
Now, we would create the growth calculation:
- Add new “Indicator” widget
- Select one field whose name and value will be shown as the MAIN TITLE and MAIN VALUE, by clicking ADD +.
- Select the same filed as the SECONDARY TITLE, Then, click on the value and choose “Quick Functions” >> % Change Over Time >> From Same Period, Previous Week:
Step 4 - Rename & Apply
The final step is to give the secondary value a meaningful name -
and click “Apply” -
And congratulation! You have a WoW comparison!
Attached please find Dash and ECdata files (x32 & x64) that illustrate the described WoW functionality.