cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

Analytical Need

There are cases where we want to analyze our measures as a comparison between periods of time. For instance, we 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 :
 
Community_Admin_5-1634556801381.png
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:
 
Community_Admin_4-1634556756281.png

 

Image 2. Dashboard display of period over period

 Attachments

- 536 KB - PoP.ecdata
Comments
EliseWoodard
Sisense Team Member
Sisense Team Member

This is super cool!

Version history
Last update:
‎10-18-2021 04:33 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request