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
Gysbert_Wassenaar

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

19,774 Views
hic
Former Employee
Former Employee
  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.
19,774 Views
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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');

19,574 Views
Gysbert_Wassenaar

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

19,574 Views
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

19,574 Views
dsc
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
19,574 Views
tsglenn22
Contributor III
Contributor III

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.

19,574 Views
hic
Former Employee
Former Employee

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
19,574 Views
tsglenn22
Contributor III
Contributor III

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
19,500 Views
tsglenn22
Contributor III
Contributor III

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
19,500 Views