Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
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

104 Comments
Anonymous
Not applicable

if you tell about your specific use case, experts here can guide you more properly.


you can use button with action selection>select in field and search string to fetch all dates less than or equal to max date


As mentioned by Henric, you should see the no of records in the as of table. You can calculate the records that as-of table will have with the sum of arithmetic series

no of records = total no of dates or days * (1+no of last day ) / 2

So if you have four years of data, your as of table will have

(4*365)*(1+4*365)/2 =  1,066,530 records


Shoaib

0 Likes
2,297 Views
Anonymous
Not applicable

Struggling with rolling months data sometimes before. Now I find the better solution. Thanks, hic‌.

And also thanks for explain the formula for the records! @Shoaib Ahmed.

Zhihong

0 Likes
2,297 Views
Anonymous
Not applicable

Hats off to Henric‌ hic‌ and Gysbert gwassenaar‌ for this super-duper As-Of table

0 Likes
2,297 Views
Olip
Creator
Creator

What about quarter? The ask is since I am looking at implementing it for rolling quarters.

0 Likes
2,297 Views
c_pannese
Partner - Creator
Partner - Creator

Hi .

Is it possible to obtaine the same result but for successive months?

For example for january 2017 I need to use the value of march 2017.

Thanks a lot.

0 Likes
2,297 Views
kinahan7
Contributor III
Contributor III

Can this method be used to compare consecutive months?  Compare December 2017 vs January 2018 or Jan vs Feb?

Thanks

0 Likes
2,297 Views
anderseriksson
Partner - Specialist
Partner - Specialist

Sure it could but why?
Both faster and easier with set analysis.

0 Likes
2,280 Views
hic
Former Employee
Former Employee

The problem of comparing two months is not solved by the As-of table, but it can be solved in a number of other ways. I would use YearMonth as dimension, e.g.

Date(MonthStart(Date),'YYYY-MMM') as YearMonth

and then calculate a change from previous month using

Sum(Sales) - Above(Sum(Sales))

This way you will get the difference between Feb and Jan to show up as the Feb number.

HIC

2,280 Views
kinahan7
Contributor III
Contributor III

I'm looking to make the calculation work with multiple dimensions in a custom pivot or ClimberCustomReport in Sense where dimensions can be added and removed.  I think aggr and above can get the desired result, but I was hoping the expression could be fluid and work with multiple dimensions.  Below is what I'm trying to do using PowerBi and the PREVIOUSMONTH function.

This type of view-

04-06-2018 12-28-16 PM.jpg

Then bring in State, County, City or Zip

04-06-2018 12-31-11 PM.jpg

0 Likes
2,280 Views
Gysbert_Wassenaar

Current month versus previous month can be done with an As-Of table. For the current month the value of MonthDiff is 0, for the previous month it will be 1.

Current month: Sum({$<MonthDiff={0}>} Sales)

Previous month:Sum({$<MonthDiff={1}>} Sales)

You could also add flag fields for this in the As-Of table:


If(MonthDiff=0,1,0) as IsSameMonth

If(MonthDiff=1,1,0) as IsPreviousMonth


The set analysis expressions will then look like

Current month: Sum({$<IsSameMonth={1}>} Sales)

Previous month:Sum({$<IsPreviousMonth={1}>} Sales)


2,280 Views