The As-Of Table

[hic]

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 ========
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.

If you instead use

Sum({$<MonthDiff={"<6"}>} Sales) / Count(distinct {$<MonthDiff={"<6"}>} Month)

you will get a 6-month rolling average:

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

[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

[hic]

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.

[rwunderlich]

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

[Gysbert_Wassenaar]

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

[stevedark]

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

[dsc]

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!

[tsglenn22]

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.

[hic]

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

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

HIC

[tsglenn22]

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.

[tsglenn22]

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!