Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

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

103 Comments
MK_QSL
MVP
MVP

Nice Article. Will try to implement !


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

Why 365.2425?

54,757 Views
anderseriksson
Partner - Specialist
Partner - Specialist

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.

54,757 Views
kji
Employee
Employee

Its the average number of days in a year

0 Likes
54,757 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

54,757 Views
hic
Former Employee
Former Employee

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

54,757 Views
flipside
Partner - Specialist II
Partner - 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?

54,757 Views
hic
Former Employee
Former Employee

You are right that the difference will only be visible over long periods. So, it's mostly an academic question whether you should divide by 365, 365.25 or 365.2425.

But my approach is: Why round at all, if you can write the correct formula from the start? Why cut a corner, when you don't need to?

HIC

45,158 Views
Gysbert_Wassenaar

Thanks for the mention of my document Hemric‌.

I'd love to see Qlik Sense generate Calendars and AsOf fields on the fly from a date dimension and make them available as master items instead of having to create them in the script.

45,158 Views
Marcio_Campestrini
Specialist
Specialist

HIC, thanks for the post.

I'll analyse to put into my applications.

Márcio

0 Likes
45,158 Views
Not applicable

1. So when to use which approach?

With As-Of you expand the datamodel, seems like a bad choice?

2. Sometimes we have both current month and Avg over serval months. If there is no data for the current month it can still make sense to show avg over serval months counting back from the current month (which you cannot select in the calendar).  How do you deal with that?  

3. HIC: Concerning "365.2425". In addition to flipsides answer, or did I got it wrong: it should be 365 for most years, and then change for some few years/periods/intervals. So 365.2425 is the lazy approach, not the correct?

0 Likes
45,158 Views