Qlik Community

Qlik Design Blog

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

The As-Of Table

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

81 Comments
MVP
MVP

Nice Article. Will try to implement !


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

Why 365.2425?

3,871 Views
anderseriksson
Contributor III

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.

3,871 Views
Employee
Employee

Its the average number of days in a year

0 Likes
3,871 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

3,871 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
3,871 Views
flipside
Valued Contributor 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
3,871 Views

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

3,871 Views
MVP & Luminary
MVP & Luminary

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.

3,871 Views
MCampestrini
Valued Contributor

HIC, thanks for the post.

I'll analyse to put into my applications.

Márcio

0 Likes
3,871 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
3,871 Views
MVP & Luminary
MVP & Luminary

1. The size of an AsOf table is almost always quite small. If you have 25 years of months you get some 45K records for an AsOf table based on months. That's very small compared to fact tables that can run into millions of records.

2. The AsOf table will often be based on a field from a Master Calendar that will contain all the dates in a period. Even if there are no records for those dates in the fact table. That's the way to deal with that.

3. If you're worried about that you could also calculate it like this:

(Year(AsOfMonth)-Year(Month))*12 + Month(AsOfMonth)-Month(Month) as MonthDiff

3,871 Views
  1. Expanding the data model is in many cases a good choice, at least from performance reasons.
  2. If you don't have data for a specific month, but still want to calculate the rolling average, the As-Of solution will do exactly what you want.
  3. All the calculation does, is to convert a specific number of days to a number of months. I.e. 28 days is a month. And 31 days is also a month. Multiplying by 12/365.2425 and then rounding to an integer will always work - hence this is the correct solution. Multiplying by 12/365 or 12/365.25 will almost always work, but not quite always. So, as I see it, multiplying by 12/365 or 12/365.25 is the lazy, incorrect approach.
3,871 Views
MVP & Luminary
MVP & Luminary

Gysbert,

I would like to see Calendaring as a native component of QV as well. In the meantime, you can use QlikVIew Components (http://qlikviewcomponents.org)

CALL Qvc.CalendarFromField ('OrderDate')

CALL Qvc.AsOfTable('OrderDate');

3,871 Views
MVP & Luminary
MVP & Luminary

Yeah, I've been using it ever since you added the AsOfTable to QVC. Thanks!

3,871 Views
MVP & Luminary
MVP & Luminary

Thanks for sharing Henric.

And it's not just accumulating dates that can be done by having a separate table hanging off in the data model.  In this blog post I cover how an average bar can be added as well as MATs and accumulations to date:

http://www.quickintelligence.co.uk/qlikview-accumulate-values/

Steve

3,871 Views
Employee
Employee

Marvellous! This has solved a tricky little dynamic lag calculation I have been wrestling with this week. Thanks Henric, and Jonathan Poole for pointing me to it!

0 Likes
3,871 Views
tsglenn22
New Contributor II

Henric,

This is a great post, thank you for all the help you provide. I've gotten this "As Of" calendar to run exactly how it should under normal scenarios. My question is how to edit the set analysis when something needs to be excluded from the summation.

I have a sum of balances that I want to show, so the normal syntax would look something like:

Sum({$<MonthDiff={"<12"}>} Balances)


However, I only want to show the balances that have been originated in the past 12 months. I have created an indicator that assigns to each month for the account a 1 or 0 to designate if it was originated in that month.


So within the set analysis, how would I work in this exclusion? Would the best route to be using an operator such as an intersection?


Sum({$<MonthDiff={"<12"}> * <OPEN_INDICATOR={"=1"}>} Balances)


Or would a p() function be the ideal way to tackle this? Or are there better options that I haven't considered?


Once again, thank you for all the help. Your blog posts have been tremendously useful.

3,871 Views

Wouldn't it be enough just to add the indicator to the set expression? Like

Sum({$<MonthDiff={"<12"}, OPEN_INDICATOR={1}>} Balances)


HIC

0 Likes
3,871 Views
tsglenn22
New Contributor II

My indicator only has a 1 in the month in which it originated, so using the OPEN_INDICATOR as its own set would only return one month and not return the past 12 months. My goal is to show the balance of new originations (originated in the last 12 months) on a trend line chart with As of Date as my dimension.  As an example, at the as of date of 9/30/2015 I want to sum up balances originated between 10/1/2014 and 9/30/2015, but at 8/31/2015 I want to sum up the balances originated between 9/1/2014 and 8/31/2015.  I’ll send you a message with an example app to help illustrate what I’m describing.  The balance I’m looking for in my example app at 9/30/2015 should be $5,796.79.

0 Likes
3,871 Views
tsglenn22
New Contributor II

The attached example I referenced in my last comment can be found in this new thread:
Rolling 12 Month Question

I was unable to attach a document in a blog post comment. Thank you for the help Henric!

0 Likes
3,871 Views
Not applicable

Love this!

0 Likes
3,871 Views
Not applicable

Great article!!

I use it and it works perfectly. It is very useful.

Thanks

0 Likes
3,871 Views
acardella
New Contributor III

Thanks for sharing!

0 Likes
3,871 Views
reinholdgraf
Contributor

Hi.

Wanted to use this function in Qliksense.

When loading, the following error-message occurs:

Table not found

tmpAsOfCalendar:

...

...

The script is as follows:

// Kalenderfunktion ergänzen

autoCalendar:

Declare Field Definition Tagged '$date'

    Fields

    Year($1) As Jahr Tagged '$year',

        Ceil(Month(Datum)/3) as Quartal,

        Month($1) As Monat Tagged '$month',

        Year($1) & '-' & Month($1) as [Jahr-Monat],

        Year($1) & '-' & Ceil(Month(Datum)/3) as [Jahr-Quartal],

        Week($1) AS Woche Tagged '$week',

        weekday($1) As Wochentag Tagged '$weekday',

        Date($1) As Datum Tagged ('$date', '$day'),

        Date(MonthStart($1),'YYYY-MMM')as xMonth,

        DayNumberOfYear($1) as Jahrestag Tagged ('$numeric');

Derive Fields From Fields Datum Using autoCalendar;

// create a list of distinct months

tmpAsOfCalendar:

  Load distinct xMonth

    Resident autoCalendar;

// cartesian product with itself       

    Join(tmpAsOfCalendar)

    Load xMonth as AsOfMonth

    Resident tmpAsOfCalendar;

// reload, filter and calculate additional fields

[As-Of Calendar]:

  Load xMonth,

    AsOfMonth,

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

    Year(AsOfMonth)-Year(xMonth) as YearDiff,

    Resident tmpAsOfCalendar

    where AsOfOmonth >= Month;

    Drop Table tmpAsOfCalendar;

What am I doing wrong ?

I am new to QS, so it might just be a beginners fault ;-)

Reinhold

0 Likes
3,871 Views
MVP & Luminary
MVP & Luminary

If you put an Exit Script; in before the first tmpAsOfCalendar line - does the autoCalendar table exist with the rows in you are expecting?  From there it all looks fine.

0 Likes
3,871 Views
reinholdgraf
Contributor

Putting in "Exit Script;" delivers no data at all.

I think, the problem is, that the master calendar is not built with LOAD; so it likely is built, when the field "Datum" is loaded later ?

0 Likes
3,871 Views
MVP & Luminary
MVP & Luminary

Perhaps - can you try moving the As Of code right to the end of the load script after everything else is loaded?

0 Likes
3,871 Views
reinholdgraf
Contributor

Yes - same result (table not found)

0 Likes
3,871 Views
reinholdgraf
Contributor

I think, the problem is, how the Master Calendar is built.

I don't know, how Henric's Master Calendar has been built. - This might help

0 Likes
3,871 Views
MVP & Luminary
MVP & Luminary

It would have just been in a LOAD statement, where Month is either a field in the main fact table or in a separate calendar table.  There are loads of scripts online for creating these.

Try Googling "QlikView Calendar Script"


Steve

0 Likes
3,871 Views