on - edited on by
DRay
Analytical Need
There are cases where we want to analyze our measures as a comparison between periods of time. For instance, ee want to know how did our measures behave on January this year vs. previous year.
This means that we have to put down in our x axis the common period (Month, Week, Day).
Modeling Challenge
But when we select the time dimension as the x axis, it will show us a sequential order of it, like so :
Image 1. dashboard without period over period
We would like to see the day name / month name/ week no. (without the year).
In order to do this, we will need to create it in advance in the cube.
Solution
In our dim date table, we will create a designated field for month name, day name, week #.
SQL for the dim date table:
SELECT DISTINCT s.OrderDate AS Date,
CASE WHEN getMonth([OrderDate]) = 1 THEN '01Jan'
WHEN getMonth([OrderDate]) = 2 THEN '02Feb'
WHEN getMonth([OrderDate]) = 3 THEN '03Mar'
WHEN getMonth([OrderDate]) = 4 THEN '04Apr'
WHEN getMonth([OrderDate]) = 5 THEN '05May'
WHEN getMonth([OrderDate]) = 6 THEN '06Jun'
WHEN getMonth([OrderDate]) = 7 THEN '07Jul'
WHEN getMonth([OrderDate]) = 8 THEN '08Aug'
WHEN getMonth([OrderDate]) = 9 THEN '09Sep'
WHEN getMonth([OrderDate]) = 10 THEN '10Oct'
WHEN getMonth([OrderDate]) = 11 THEN '11Nov'
WHEN getMonth([OrderDate]) = 12 THEN '12Dec'
ELSE '' END AS OrderMonthWithNum,
CASE WHEN DayOfWeek(OrderDate) = 1 THEN '01Sunday'
WHEN DayOfWeek(OrderDate) = 2 THEN '02Monday'
WHEN DayOfWeek(OrderDate) = 3 THEN '03Tuesday'
WHEN DayOfWeek(OrderDate) = 4 THEN '04Wednesday'
WHEN DayOfWeek(OrderDate) = 5 THEN '05Thursday'
WHEN DayOfWeek(OrderDate) = 6 THEN '06Friday'
WHEN DayOfWeek(OrderDate) = 7 THEN '07Saturday'
ELSE '' END AS Day,
WeekOfYear(s.OrderDate) AS Week
FROM [SalesOrderHeader] s
We will use the new fields as our x axis fields.
The reason we have created the month & day fields with the number prefix, is in order for them to be sorted correctly in the dashboard (we used the Chart Label Order Changer plugin for that purpose).
Dashboard:
Image 2. Dashboard display of period over period
Attachments
- 71 KB - PeriodOverPeriod.dash
- 536 KB - PoP.ecdata
