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

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

mceclip2.png 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.

mceclip3.png Solution

In our dim date table, we will create a designated field for month name, day name, week #.
SQL for the dim date table:

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' 

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).
Image 2. Dashboard display of period over period


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

This is super cool!

Version history
Last update:
‎02-22-2024 11:52 AM
Updated by:
Community Toolbox

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

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: