This need comes up a lot in the questions that people ask on the forum…
Imagine that you are working with Sales data, presented monthly, and you need to build a chart that shows monthly sales, and next to it, average sales for the last 6 months.
How would you calculate average sales for the prior 6 months, compared to the month listed as a Dimension?
The first thought is to use Set Analysis, but it’s not helpful in this case, because, as we all know, Set Analysis only gets performed once per chart. Therefore, Set Analysis condition cannot be sensitive to the values of a chart dimension.
The “trick” in this case is to create such a data model that enables defining the same condition (“last 6 months compared to the given Month”) in a form of a “static” field comparison, that wouldn’t be dependent on the dimension values. Then, it can be used in Set Analysis.
This form of a data model is possible using a technique that we call “As of Date” table. The name describes a solution that enables calculating various date conditions, such as YTD, QTD, Last 6 months, … as of a certain date, not necessarily as of today.
In order to build the “As of Date” table, we need to perform the following steps (in this example, our table will be built at the monthly level):
In our example, Months are defined as Date fields, represented by MonthStart() for each transactional date. Therefore, we can use all QlikView functions that are applicable to Dates.
join (TransactionMonths) load * resident DisplayMonths;// 4. Reload the same table and calculate all the necessary flags: MonthsLink: Load Month, DisplayMonth, IF( Month >= AddMonths(DisplayMonth, -6) , 1, 0) as Last6MonthsFlag, IF( Month >= AddMonths(DisplayMonth, -12) , 1, 0) as Last12MonthsFlag, IF( Month = DisplayMonth , 1, 0) as SameMonthFlag Resident TransactionMonths ; drop table TransactionMonths;
Notice the flag that we called SameMonthFlag. It will be equal to 1 when DisplayMonth and Month are the same. This will be useful for calculating Sales for “the same” month – the equivalent of the simple sum(Sales) in regular data models that don’t use this technique. Since we are building the associations between the Display Month and the Transaction Month manually, we need to take care of that manually as well.
Now we are ready to build the chart that shows Monthly sales, compared to the 6 month average:
Create a new Chart, select Straight Table as the chart type. Use the new field DisplayMonth as the Dimension. The expressions will look as follows:
(you may come up with an easier way of calculating the average for the last 6 months, but we opted for the Advanced Aggregation option)
In summary, using the “As of Date” technique helps to overcome the known limitation of Set Analysis, caused by the fact that Set Analysis gets only calculated once for the whole chart, and therefore it cannot be sensitive to the values of the individual dimensions. Using the extra table and the “artificial” association between the Transaction Month and the Display Month allows defining simple Flags that make the same condition “Static”, i.e. independent of the individual dimension values.
If you found this blog post helpful you may want to read the document I posted on community.qlikview.com a while ago:
Calculating rolling n-period totals, averages or other aggregations (http://community.qlikview.com/docs/DOC-4252)
Yes, this is an excellent work around! My goal here was to point out the unexpected behavior of the button “Back”.
I might have missed something but should you also add a Month <= DisplayMonth to your flag field calculation?
Without that, only the greatest 12 display months will include correct figures, all months (dates) before greatest month – 12 months will include months (dates) in the "future".
Thanks for a nice tips and tricks site.
thanks for the comment. You are probably right, those examples are more directional than precise. Perhaps this dataset only has data for past months (i.e. Sales with no forecast) and doesn’t look into the future. Point taken, though…