Seasonality Chart—Simple as That
Comparison By Year
Comparison By Year
Several times I have been asked for a chart with the possibility to compare seasonality or month over years. It´s proven to be both popular and useful in its own simplicity. In a regular line chart, you’ll have all time periods after one another, whereas, in this version, you´ll be able to compare each month to each other in different years.
For this set up I´ll use five modules, two for the chart and calculation and one source. I will also need to create a list with all the months and one line-item subset (LIS) from the source.
The first module I create is the SYS month map. This will be used in a lookup formula later, and the purpose is to map the month in the list I have created to the years in the Time dimension. As you can see below, the month of ‘Jan’ in FY19 is mapped to time period ‘Jan 19’. For performance reasons, I have also created two more system modules—one for the Month name (Jan, Feb…) and one for the year (19, 20, 21, 22).
To add more details to it let´s look at the formulas in the module:
The first ‘month as text’ will give the written time period that will be used in the finditem formula in the second line item. Why do I split them apart? For performance reasons. This is not by any means a massive calculation, but it's still worth taking into consideration.
'SYS140 Month properties'.Code & 'SYS130 Time Year'.Year
The first part of the formula will get the code from the SYS140 Month properties; this is the code from list with the month.
I will concatenate this with the second part of the formula, which will give the year. In the SYS130 Time Year module the Period(Start()) formula will give the starting period of the time period, which is Jan 19, Jan 20, Jan 21… With the RIGHT() formula I will get the year. Why do I take the last three letters when the year only has two? In the next step I use this text to get the time period with a finditem, and for this I need a space in between month and year. By taking the last three letters I will automatically get a space in between. If I didn´t do it like this, I would have to add an extra element in the concatenation 'SYS140 Month properties'.Code & “ “ & 'SYS130 Time Year'.Year
The second line item will find the time period for the combination of month (‘SYS month’) and Time period (year) as shown in the table above.
Then the formula is FINDITEM(Time, Month as text) —I use the text string I just created (month as text) and look in the Dimension Time. Time in this case I use the built-in native dimension for time.
On to the next module which is the chart module. This module will also have two line items, one to collect data from the source. This one has time scale month, and the other line time to map the collected values to the month in our list, ‘SYS month’.
Formulas! The first one, Collect(). Well, it collects values from a source module using a Line item Subset (LIS). Why do I use LIS? It´s an extremely powerful and effective way of getting the data we need, and the calculations are already done. Remember; Calculate one reference many times!
For more information on a line item subset, see this article in Anapedia.
The second line item (Time scale, Year) is the one used in the chart (Values). The formula Collect[LOOKUP: SYS Month map.Find month], will take the collected values and map to with a Lookup from the first module we created. This will give the result month (from ‘SYS month’) over time (year). In this example, I have the dimensions from my LIS (the P&L) and the department hierarchy. This means I can see the seasonality/development for each department on each P&L line month by month for the year in the model.
Last but not least, we will publish this in the UX. I use custom views as this usually will not limit the end user experience. Now this is a chart so there is not much to do, like pivot for instance, but I take this as a rule and use saved views only when it's absolutely needed
In this chart, I need to hide a few columns, which is easily done when building your chart view.
A good thing is that if you want to use this type of chart for several areas you only have to copy the last module and change your Line item subset. The other lists and modules can be referenced again.
Comments
-
Very nicely prepared! Thanks for sharing
0 -
This is great! I recently took over a few models that were designed to have a separate line item for each year. Needless to say, I wanted to move away from that to a design that follows best practices. The only issue was that people didn't want to lose their stacked line charts. With the help of this article I can get rid of the separate line items for each year and keep the stacked line charts people love!
0 -
Thank you, Fabian.
Is there a way to build the Q/Q variance chart (without time dimension)? Below is screen shot.It seems to me it should be:
> Quarters dimension (list)
> Line Items
> Timescale - noneThank you for the thoughts/sharing.
0
