March 19, 2019 at 12:26 pm #545FrankKeymaster
- Topics Started - 80
- Replies Written - 53
How can I easily see last months value for something like Sales so I can do a month to month comparison when I’m using time as a dimension (where you have Months or Weeks or even Days going across the top of the display)?
It’s easy to do this in Stratum, and there are at least 2 answers to the question:
1. – Use Time Ranges, yeah yeah I know the question was about NOT using Time ranges but…. You could set up a bunch of measures, each representing a month and then do the math between each of them. This would get really tedious if you were doing this for weeks, but it would work! This is not the way I recommend but it will work and does not require any advanced features.
2. – Use a tuple (lots of posts on these already but we’ll include it here too) and the concept of a Lag. Lags allow us to go back (or forward) thru the values in a given dimension. They make the most sense when using time as a dimension since time is normally sequential, so you move thru it from one period to the next.
So….lets assume I’m looking at Product Category sales this year and want to compare each months sales to the previous months sales, only displaying months that have sales in them this year. The end result would look something like:
So the basics first:
* Categories in the rows.
* Year Based Months Based in the columns – with a filter for Current Year and Last Year (we need to include last year in the filter so that in Jan of this year we can reach back and get Dec from last year)
* Measures properties have been changed to Time Range = NO and Measures are on Rows (this is not important – it just made the graph easier)
* The only Regular Measure is Sales Amount
* We started at Year in the columns and then drilled down on the current year
* The Current year in this example is 2017
You’ll also note there is an axis filter on the Columns – this was to prevent showing Months that have no Sales data for the month. That filter looks like (this is not required – just helps to de-clutter the view)
[Measures].[Data4 (Sales Amount)]<>0 and goes in the Axis filter for the columns.
The calculated measures are:
1. Last Months Sales
This is the calc where we will use the Lag technique – essentially saying go back and grab the data from one period back. It looks like:
([Time].[Year Based Months Based].Lag(1),[Measures].[Data4 (Sales Amount)])
The first part is our Time dimension – and by specifying Lag(1) we are indicating we want to go back (if you wanted to go forward you’d say Lag(-1) – which seems backwards – but is a MicroSoft thing – so go figure) one period.
The second part of the tuple after the , is the measure.
All of this enclosed by the parens (….,…) – this complete calc is a tuple. The time dimension uses the Lag(x) to select a specific offset from the month in each column.
2. % of Last Months Sales – I’m going to assume you know how to handle this one.
The graph is a Combo graph that shows the Sales on the primary axis and the % on the secondary axis.
Hope this helps – post any questions here and we’ll respond here.
You must be logged in to reply to this topic.