Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER

Last week I wrote about how the Above() function can be used for calculating rolling averages and other accumulations. There is however also an alternative method for doing the same thing:

The As-Of table.

When you use the Above() function, you fetch a number from other rows in a chart or Aggr() table. The As-Of table is slightly different in this respect: It is not a transient table created by an object or an expression – instead it is a real table in the data model.

The idea is to create a secondary month field – the AsOfMonth - that links to multiple real months.




In the example above, you can see that ‘2015 Oct’ links to several preceding months, and each Month in turn links to several rows in a fact table. This means that a specific transaction will be linked to several AsOfMonths.

In the data model, the As-Of table should appear as a separate calendar table that links to the existing primary calendar table:




One way to create this table is the following:

First, make sure that you in your master calendar have a field “Month” that is defined as the first date of the month, e.g.


Date(MonthStart(Date),'YYYY MMM') as Month,


Then add the following lines at the end of the script:


// ======== Create a list of distinct Months ========
Load distinct Month
  Resident [Master Calendar] ;

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month as AsOfMonth
  Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month,
  Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
  Year(AsOfMonth)-Year(Month) as YearDiff
  Resident tmpAsOfCalendar
      Where AsOfMonth >= Month;

Drop Table tmpAsOfCalendar;


Once this table has been created, you can use the AsOfMonth as dimension in charts where you want rolling averages and accumulations.

If you as measure use


Sum({$<YearDiff={0}>} Sales)


you will get a yearly accumulation – year-to-date up until the day of the script run.


Yearly accumulation.png


If you instead use


Sum({$<MonthDiff={"<6"}>} Sales) / Count(distinct {$<MonthDiff={"<6"}>} Month)


you will get a 6-month rolling average:


Rolling average.png

And finally, if you use


Sum({$<MonthDiff={0}>} Sales)


You will get the real, non-accumulated numbers.

I have made the Set Analysis expressions based on two fields: YearDiff and MonthDiff. However, for clarity it could be a good idea to add flags in the As-Of table, so that the Set Analysis expressions become even simpler, e.g.


If(MonthDiff=0,1,0) as IsSameMonth,
If(YearDiff=0,1,0) as IsSameYear,
If(MonthDiff<6,1,0) as IsRolling6,


Summary: The As-Of table is a good way to calculate rolling averages and accumulations.




Further reading related to this topic:

The Above Function


Calculating rolling n-period totals, averages or other aggregations


Does anyone have a suggestion for how to handle multiple months filter confusion with end users.  What I mean is that my user selects the month field from the master calendar to filter the dashboard but this breaks the chart using the asofmonth field.  The user then has to remove the month filter applied through the master calendar and apply the asofmonth filter to filter the chart using the asofmonth field.

Specialist III
Specialist III


Have you tried either

  • using a {<Month = , >} in the set analysis for the measures referencing the Asofmonth field
  • Or using Alternate states

Hi Henric,

Thanks for this great article! It is very useful!


I have a question please: I have a graphic where I want to display the 12 previous months according to the selected YearMonth (YMAO field) --> there is only one selected YMAO value.

I'm using this formula:

Sum({<MonthDiff={">=0<12"}>} Sales)

it shows only one point (for selected YMAO) with the total sales for 12 months. This total is correct, but I want to display the other months in the graphic in order to have sales by each month. I tried with this formula, but I get the same result as before:

Sum(Aggr(Sum({<MonthDiff={">=0<12"}>} Sales), YMAO))


Do you have any suggestion?

Thanks in Advance,


Specialist III
Specialist III

Hi @elinn08 


Do you mean something like this?


Line Chart.JPG


Hi Robert99,

Yes, something like that

I added to the graphic the date field that links the as-of table with the calendar table and it seems to work

If you have another suggestion, please let me know



Specialist III
Specialist III


How did you do it using AsofTable? I would prefer to do it this way if possible.

I did this using Canonical Dates. 

The client wanted to show the last 15-18 MonthYears with the prev 2 years showing. I eventually did this using the Canonical date approach (with the CanonDate joined to the calendar).

And CanonType used in the measure to determine the curr vs previous years

LINK_FactTable ,
DocPostingDate AS DateCanon,
'INVOICE' as CanonType //This si both invoiced and shipped. Its for all dates. Should be CurrYear
resident FACT_TABLES
// where DataType = 'InvOrCredits'
DATEBridge: //prev year
Concatenate (DATEBridge)
LINK_FactTable ,
addmonths(DocPostingDate,+12) AS DateCanon,
'InvPastYear1' as CanonType
//InvoiceSalesNet as InvSalesNetPrevYr
resident FACT_TABLES

DATEBridge: //prev year *2
Concatenate (DATEBridge)
LINK_FactTable ,
addmonths(DocPostingDate,+24) AS DateCanon,
'InvPastYear2' as CanonType
// InvoiceSalesNet as InvSalesNetPrevYr2
resident FACT_TABLES