Qlik Community

Qlik Design Blog

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

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.

 

Fields.png

 

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:

 

DataModel.png

 

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 ========
tmpAsOfCalendar:
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,
  AsOfMonth,
  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.

 

HIC

 

Further reading related to this topic:

The Above Function

Accumulations

Calculating rolling n-period totals, averages or other aggregations

91 Comments
MVP
MVP

Nice Article. Will try to implement !


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

Why 365.2425?

20,350 Views
Partner
Partner

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.

20,350 Views
Employee
Employee

Its the average number of days in a year

0 Likes
20,350 Views
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...)

Regards,

Martijn

20,350 Views

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.

HIC

0 Likes
20,350 Views
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?

0 Likes
20,350 Views