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.
我创建的第一个模块是SYS月地图。这将在以后的查找公式中使用，目的是绘制我创建的时间尺寸列表中的一个月。如下所示，2019财年的“ Jan”月映射到了“ 1月19日”的时间段。出于绩效原因，我还创建了两个系统模块 - 一个月份的名称（2月，1月），一个是年份（19、20、21、22）。
为了添加更多详细信息，让我们查看模块中的公式：
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.
我将将其与公式的第二部分相连，这将给这一年。在里面SYS130时间年模块期（start（start（））公式将提供时间段的开始期，即1月19日，1月20日，1月21日…带有右（）公式I将获得这一年。当一年只有两个时，为什么我要拿最后三个字母？在下一步中，我使用此文本与FindItem一起获得时间段，为此，我需要在一个月和一年之间的空间。通过拿最后三个字母，我将自动在两者之间获得一个空间。如果我不这样做，我将不得不在串联中添加一个额外的元素'SYS140 Month properties'.Code &“ “ &'sys130时间年年
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 isFINDITEM(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 inAnapedia.
The second line item (Time scale, Year) is the one used in the chart (Values). The formula收集[查找：系统月地图。找到月份]，将收集到的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 usesaved 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.
