Author: Joyce Zhou
Introduction
In this article, let's look at how to create a Kepion report with Week Over Week (WoW), Month Over Month (MoM), and Year Over Year (YoY) comparisons. For more information on Year to Date and Month to Date calculations, refer to this article.
Note: This is NOT a general tutorial on calculating WoW, MoM, and YoY. You can refer to this article if you are not a Kepion user.
The column definition in this Form is pretty complicated. We can easily tell we have Time and Scenario on the column.
What makes it even more interesting is that the Change columns are not the variance between the Actual and Plan scenarios, otherwise we would be able to easily get this done using a scenario formula. Instead, it's the variance of Actual data compared to the previous time period (Week, Month, or Year). The approach we'll take instead is to include a third Dimension on the column: Metric.
Example
1. Configure the Column definition:
There are some value overrides in the column headers:
With some formatting, we have established the Form's structure.
2. Add the MDX calculation for Time Type.
/*------------------------------*/
/* TimeType */
/*------------------------------*/
-- Note: These calculation must be defined for each Time hierarchy that is used. Currently defined for:
-- - Fiscal Calendar
-- - Annual Calendar
Scope
(
{
[Scenario].[Scenario].[Actual]
,[Scenario].[Scenario].[Plan]
}
,[Metric].[Metric].[Amount]
);
/*--------
Previous Week (Rolling 7 Days)
*/--------
-- Days - YTD
Scope
(
[Time Type].[Time Type].[Previous Week (Rolling 7 Days)]
,Descendants( [Time].[Fiscal Calendar].[All], 4, SELF)
);
THIS = Aggregate(([Time].[Fiscal Calendar].CurrentMember.Lag(6):[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
/*--------
MTD
*/--------
-- Days - YTD
Scope
(
[Time Type].[Time Type].[Month to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 4, SELF)
);
This = Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalMonth]
,[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
-- Months - YTD
Scope
(
[Time Type].[Time Type].[Month to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalMonth]
,[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
/*--------
YTD
*/--------
-- Days - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 4, SELF)
);
This = Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
-- Months - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember)
,[Time Type].[Time Type].[Periodic]);
End Scope;
-- Years - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 1, SELF)
);
This = [Time Type].[Time Type].[Periodic];
End Scope;
End Scope;
This is the Form we get:
3. Add in the MDX calculation for the Change.
Scope
(
[Scenario].[Scenario].[Actual]
,[Metric].[Metric].[Change]
);
/*--------
Previous Week (Rolling 7 Days)
*/--------
-- Days - YTD
Scope
(
[Time Type].[Time Type].[Previous Week (Rolling 7 Days)]
,Descendants( [Time].[Fiscal Calendar].[All], 4, SELF)
);
THIS = Aggregate([Time].[Fiscal Calendar].CurrentMember
,[Metric].[Metric].[Amount])
/
Aggregate([Time].[Fiscal Calendar].CurrentMember.Lag(6)
,[Metric].[Metric].[Amount])
- 1;
End Scope;
/*--------
MTD
*/--------
-- Days - YTD
Scope
(
[Time Type].[Time Type].[Month to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 4, SELF)
);
This = Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalMonth]
,[Time].[Fiscal Calendar].CurrentMember)
,[Metric].[Metric].[Amount])
/
Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalMonth]
,ParallelPeriod([Fiscal Calendar].[FiscalMonth], 1, [Time].[Fiscal Calendar].CurrentMember))
,[Metric].[Metric].[Amount])
- 1;
End Scope;
-- Months - YTD
Scope
(
[Time Type].[Time Type].[Month to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalMonth]
,[Time].[Fiscal Calendar].CurrentMember)
,[Metric].[Metric].[Amount])
/
Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalMonth]
,ParallelPeriod([Fiscal Calendar].[FiscalMonth], 1, [Time].[Fiscal Calendar].CurrentMember))
,[Metric].[Metric].[Amount])
- 1;
End Scope;
/*--------
YTD
*/--------
-- Days - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 4, SELF)
);
This = Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember)
,[Metric].[Metric].[Amount])
/
Aggregate(PeriodsToDate([Time].[Fiscal Calendar].[FiscalYear]
,ParallelPeriod([Fiscal Calendar].[FiscalYear], 1, [Time].[Fiscal Calendar].CurrentMember))
,[Metric].[Metric].[Amount])
- 1;
End Scope;
-- Months - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 3, SELF)
);
This = Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalYear]
,[Time].[Fiscal Calendar].CurrentMember)
,[Metric].[Metric].[Amount])
/
Aggregate(PeriodsToDate( [Time].[Fiscal Calendar].[FiscalYear]
,ParallelPeriod([Fiscal Calendar].[FiscalYear], 1, [Time].[Fiscal Calendar].CurrentMember))
,[Metric].[Metric].[Amount])
- 1;
End Scope;
-- Years - YTD
Scope
(
[Time Type].[Time Type].[Year to Date]
,Descendants( [Time].[Fiscal Calendar].[All], 1, SELF)
);
This = ([Time].[Fiscal Calendar].CurrentMember
,[Metric].[Metric].[Amount])
/
(ParallelPeriod([Fiscal Calendar].[FiscalYear], 1, [Time].[Fiscal Calendar].CurrentMember)
,[Metric].[Metric].[Amount])
- 1;
End Scope;
End Scope;
4. Add in the KPIs.
You should have the final report: