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):

1. Extract a distinct list of Months, associated with the Transaction date.
2. Create a distinct list of Months that will be displayed in the Charts (the “Display Month”). In most cases, the two lists can be the same.
3. Create a list of all the possible combinations of the Transaction Month and the Display Month, using Cartesian join.
4. For each pair of months, calculate the necessary flags.

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.

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.