cancel
Showing results for
Did you mean:

# Analyze Period over Period - MoM / WoW / DoD

Community Team Member

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

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

- 536 KB - PoP.ecdata
Sisense Team Member

This is super cool!

Version history
Last update:
‎03-02-2023 08:50 AM
Updated by:
Contributors
Community Toolbox

Developers Group:

Product Feedback Forum: