Qlik Community

Ask a Question

Qlik Design Blog

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

Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES

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


Nice Article. Will try to implement !

Round((AsOfMonth-Month)*12/365.2425) as MonthDiff

Why 365.2425?


That is the average length of a year considering leap years.

You might think it should be 365.25 since every 4th year is leap year but that is not quite the fact.

Since the Earth rotates around the Sun slightly faster there there are years that are not leap years even though they are 4th year.

If the year is dividable by 100 it's not a leap year but again if it's dividable by 400 it is!

This makes an average year = 365.2425 days.


Its the average number of days in a year

Not applicable

Hi Manish,

The 365.2425 is because there's usually 365 days in a year, but one in every 4 years is a leap year (having 366 days), but... every century this leap day is skipped, except once every 400 years this leap day is not skipped. (See https://en.wikipedia.org/wiki/Leap_year)

Correcting for leap years (and the missing leap years every 400 years) you'll get an average year length of 365 + 1/4 - 1/100 + 1/400 = 365.2425 days. (Ofcourse to be very precise, you'd have to account for 'leap seconds' as well...)




All above comments about leap years are correct. For those of you who are Calendar aficionados, you should read the very last section of QlikView Date fields - the one labelled Inter gravissimas - The Gregorian Calendar.

Leap seconds are however different. Leap seconds will not affect Qlik calendars. It doesn't matter how many leap seconds are added in the real world, new days will still start at integer values of the date serial number.


Specialist II
Specialist II

I may have misunderstood the full logic, but I calculate it will take over 102 years for the calculation to differ between 365.25 and 365.2425. In fact it takes 60 years for 365 to change it, maybe it will only be needed for certain requirements!! Also, won't 365.2425 only be needed if dates range over the years 2000 or/and 2400?