January 9, 2018 at 11:37 am #463FrankKeymaster
- Topics Started - 62
- Replies Written - 50
I want to compare this year vs. last year with months going down the side and see the % of sales ratio.
We get this question from time to time and it’s pretty easy to do. You could do this by describing 24 separate measures and then adding 12 more for a total of 36 that do the calculation between the last year and this year sales. But there is an easier way! All you need to do is use time as a dimension (or in Stratum terms – set Time Range to No) and create 3 measures that you want to repeat for every month (or week or any other time unit you choose).
First – what do you mean – use time as a dimension? This allows you to have measures the automatically repeat for each time unit displayed. Simple example would be displaying sales and units for each month of a year, or weeks or days…
To start – you have to set Time Range to No – this is a property of the Measures node in the View Explorer. Easy to do, just right click on Measures in the View Explorer (VE) window and select properties – then change the Time Range to No, the default is Yes so each time you want to use Time as a dimension you’ll need to set this to No.
Next you’ll want to include some Time into your view. So instead of having time controlled by column (as in a ‘normal’ view where you select the from and to time for each measure) we are going to have each measure repeated for every time unit we select. In the case of my example we have years and months. I always recommend using the ‘Year Based Months Based’ time dimension since these based (or relative) time dimensions allow you to select Current Year and Last year and will automatically switch years when the year changes. Same with the months – Current Month, Previous Month etc. I’ll post a picture of my view with the VE open so you can see which stuff I selected.
Now we need to get a measure or two – in my example we’ll just grab Sales Amount and then do a couple of calcs to see Current Year (CY) and Last Year (LY). Then we’ll create a % calc between CY and LY.
So….if we were interested in just a single year – we could just select the Sales Amount measure and we’d be done. But because we are going to select a couple of years we need to create a simple calc that filters our CY and LY sales. These calcs are called ‘Tuples’ – no idea why…..
The calc for Current Year looks like this:
// Tuple of Current year sales
([Time].[Year Based Weeks Based].[Year Based].[Current Year],[Measures].[Data4 (Sales Amount)])
the // Tuple of…. is just a comment in the calc
The [Time].[Year Based Weeks Based].[Year Based].[Current Year] is from the dimensions – this is filtering the Time dimension to show only the Current Year
The [Data4 (Sales Amount)] is the Sales Amount measure I want to see.
Put a comma between them and add parens at the front and back and you’ve got yourself a Tuple.
Next created the same calc for last year:
// Tuple of last years sales
([Time].[Year Based Weeks Based].[Year Based].[Last Year],[Measures].[Data4 (Sales Amount)])
Finally created a calc to do the % calc using a Stratum function – I used the function so that the divide by zero check is automatically handled. There are some good functions in the Stratum Viewer function area of the calculation window (Expression window) so check ’em out.
#AchievementPercent([Measures].[Data9 (Current Year)], [Measures].[Data8 (Last Year)])
You can add formatting and captions to these as you usually would for a calc’d measure.
So using those 3 calcs I’m able to show data for each month thru the current month without having to define a calc for every month. In my example I’ve got CY and LY sales and the %, by Division (across the top) by month (down the side) and the total for the company – totals to the right, and totals for the year on the bottom. Views like this are not only great for month to month compares but also for creating charts that show trends.
So if you’re still with me – as this post has become more of a novel than a post…. Another ‘trick’ I used was to limit the months of data that were displayed using an expression that will automatically only show months thru the current month – you can see that the Months Based are filtered via Expression in the View Filter area in the view. This was described in detail in a previous post which you can take a look at here . Here is the actual expression filter I used in this view:
// Only show the Current and past months
([Time].[Year Based Months Based].CurrentMember.name = ‘Current Month’ OR
[Time].[Year Based Months Based].CurrentMember.name = ‘Previous Month’ OR
(INSTR(1, [Time].[Year Based Months Based].CurrentMember.name, ‘Ago’)<>0))
Post any questions or comments and we’ll respond here.
Even tho this was a novel I promise this is an easy way to do this:)
- This topic was modified 2 years, 6 months ago by Frank.
November 21, 2018 at 12:57 pm #532FrankKeymaster
- Topics Started - 62
- Replies Written - 50
An easy way to show only YTD months or weeks when displaying information with the months or weeks going across the top or down the side.
I wanted to add an update to this post. When I initially suggested that you can use an expression to filter the months to only see those months that have passed and the current month, so that the future months without sales don’t show up (which is nice when comparing TY to LY particularly in a chart) I was right (just to be clear) that you can do it this way – but it’s also not the easiest way to do this.
Instead of the expression filter you can use what is known as a “Named Set” filter. These named sets are available for the Time dimensions like Months, Weeks and Days (without any Year associated with them – as I used in the example). Named Sets are a list of time units (Months, Weeks, Days normally) that Stratum automatically updates to include months from the beginning of the year thru the current month. You can filter the Months dimension on a named set by selecting to filter Months, then change the “Filter Type” which you can do by selecting the More button in the filter window. Just select Named Set and then pick one of them. In most cases it won’t matter which named set you select, there is a named set created for each type of monthly data you import, but in most cases they are all the same.
Post if you have any issues or questions. Hope this helps.
You must be logged in to reply to this topic.