DeepSee - Period to date vs same period last year
How are we doing THIS year versus the same period LAST year?
This is a common need in Business Intelligence. In fact, many design specifications for reports make use of a comparison between a selected period (year, quarter, etc) up to a certain date (for example November 15th, 2016) and a summary of the same information for the previous year (i.e. up to November 15th, 2015).
This post shows how to implement this in DeepSee.
The following is an example using the HoleFoods cube. The table shows sell counts for each Product Category in rows. The first column shows the sell counts in 2016 up to the date of today. The second column shows the sell counts in 2015 up to exactly one year ago.
In this example the period of interest is year, corresponding to the “YearSold” level in HoleFoods but we will implement a dynamical period that can be set to quarter, month, etc.
This implementation is not trivial but it can be achieved on a “normal” non-KPI pivot
Prerequisites
For this implementation you will need a standard calendar, that is a calendar based on Months and not on custom periods.
A week level in the same hierarchy as day and month will lead to unexpected results. If a week period is needed the week level should be placed in a different hierarchy than the rest of the levels.
Define the Period as a pivot variable
In Analyzer Define a Period pivot variable as in the following picture:
Define PTD and LYPTD
Define two Calculated Measures to compute period to date (PTD) and the same period in the last year (LYPTD):
- PTD (Period To Date) with Expression:
%OR(PERIODSTODATE([DateOfSale].[Actual].[$variable.Period], [DateOfSale].[Actual].[DaySold].&[NOW]))
PERIODSTODATE is an MDX function returning the set of child or descendent members of the given level, up to and including the given member. In the example above PERIODSTODATE returns a set of dates in the period specified by the pivot variable Period up to the date of today. %OR is used to return a single value for the set of all dates.
- LYPTD (Last Year Period To Date) with Expression:
%OR(PERIODSTODATE([DateOfSale].[Actual].[$variable.Period], PARALLELPERIOD([DateOfSale].[Actual].[YearSold], 1, [DateOfSale].[Actual].[DaySold].&[NOW])))
PARALLELPERIOD is used to calculate the date of today but one year in the past. This date is used in PERIODSTODATE as it was done in PTD to return a set of dates in Period but one year in the past. %OR combines all elements of this set of dates in one single value. Notice that $variable.Period is how you can reference the value of the Period pivot variable.
A simpler solution to calculate the date of today but one year in the past is to use the following syntax together with the NOW member:
[DateOfSale].[Actual].[DaySold].&[NOW-1y]
The expression becomes:
%OR(PERIODSTODATE([DateOfSale].[Actual].[$variable.Period], [DateOfSale].[Actual].[DaySold].&[NOW-1y]))
Create the pivot
Place Product Category on Rows, Count on Measures, crossjoin PTD and LYPTD on Count. Drag the Period pivot variable to filters and select a period, for example YearSold. Finally, click on the gear icon next to PTD and LYPTD in Columns to change the captions. It is possible to use $variable.Period in the caption string (not shown).
The MDX for this example is the following:
WITH MEMBER [DateOfSale].[PTD] AS '%OR(PERIODSTODATE([DateOfSale].[Actual].[$variable.Period], [DateOfSale].[Actual].[DaySold].&[NOW]))' MEMBER [DateOfSale].[LYPTD] AS '%OR(PERIODSTODATE([DateOfSale].[Actual].[$variable.Period], [DateOfSale].[Actual].[DaySold].&[NOW]))' SELECT NON EMPTY NONEMPTYCROSSJOIN([Measures].[%COUNT], {%LABEL([DATEOFSALE].[PTD],"PTD",""), %LABEL([DATEOFSALE].[LYPTD],"LYPTD","")}) ON 0, NON EMPTY [Product].[P1].[Product Category].Members ON 1 FROM [HOLEFOODS]
Embed the Pivot in a dashboard
Finally, create a dashboard and embed your pivot as a widget. Add an Apply Pivot Variable Control targeting Period on your widget to be able to change period. This is how the widgets appear as a Bar Chart:
Exercises for the reader
- Extend this implementation to be able to select a dynamic date so that sales can be shown up up to a user-selected date. Hint: create a Date pivot variable.
- Can you find an alternative to the method proposed in this post, for example using %MDX in calculated members? Post your answer in the comments.