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

[#2]

Hi i have created As of calender and i'm facing an issue

Here Month1 is the list box i'm providing for the user to select

when i select a Month in Month1 the as of Month should go to the back ward 6 months

But here it is showing the forward 6 months

What i want is

[hic]

No, this is the way it should be. Just swap position of the two list boxes and you will get what you want.

Look at it this way: A specific transaction belongs to a specific Month (in the standard calendar). This transaction should be included in the rolling-6 calculation not just this month, but also 5 months ahead in time. Hence, the month 'Nov 2015' should be linked to As-Of Months in the future.

The 'AsOf Month' can then be used in a chart for the rolling-6 calculation and will then link backward in time to older transactions.

So it is correct the way it is.

HIC

[#4]

Thank you so much I was struggling with the below senario

In June the top two profitted cuntries are Argentina and Japan

In May the top two profitted cuntries are America and Argentina

What i want is when i click on jun i want both june and may data to dispaly and the comparision should be between the selected month (jun) and top two values of it  Argentina and Japan to Argentina and Japanin May

[#5]

Hi Henric

I have used the As-of calender  in my script

my association is like this

but in the diagram you provided the association is in  working correct

my code is working in the reverse order

can you please help me

keytable:
load * Inline [
OrderDate
42186
42217
42248
42278
42309
42339
42370
42491
];


Temp: 

Load 

min(OrderDate) as minDate,

max(OrderDate) as maxDate 

Resident keytable; 



Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 



TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 



MasterCalendar: 

Load 

TempDate AS OrderDate, 
week(TempDate) As Week, 

Year(TempDate) As Year, 

Month(TempDate) As Month, 

Day(TempDate) As Day,
Date(MonthStart(TempDate),'YYYY MMM') as Month1,

Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar;
tmpAsOfCalendar:
Load distinct Month1  Resident MasterCalendar ;



// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month1 as AsOfMonth
Resident tmpAsOfCalendar ;



// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month1,
AsOfMonth,
-([AsOfMonth]<>[Month1]) as [IsPrev51],
Round((AsOfMonth-Month1)*12/365.2425) as MonthDiff,
// Date(Date#(MonthYear,'MMMYYYY'),'YYYYMM') as MonthYearq,
  Year(AsOfMonth)-Year(Month1) as YearDiff
Resident tmpAsOfCalendar
Where  AsOfMonth >= Month1;

[Gysbert_Wassenaar]

Make the selection in the AsOfMonth field instead of the Month1 field.

[#7]

Hi Gysbert,

In the As-of month, i don't have data for a particular month, so it should be grey in color as per qlikview basic

its not happening, so it might confuse the end user and

i'm showing rolling sums

if i click on a month and it has no data, that month  is not displaying in the chart where as the previous 5 months data is coming

if i don't have data in the current month the previous 5 month data also should not display

can you please help me

[Anonymous]

Hi guys,

I implemented this method but i don't now how i build the expression:

Concretely i want create 2 measures on my dashboard:

1 -  Compare this year vs last year until now ex: [01-jan-2015 to 15-nov-2015] vs [01-jan-2016 to 15-nov-2016]

ex: YTD vs YTD (year-1) ??

2 - Compare this month vs last month until now ex:[01-oct-2016 to 15-oct-2016] vs [01-nov-2016 to 15-nov-2016]

ex: MTD vs MTD (month-1)

Same period comparison. 

Can you help me ??

Thanks in advance.

[#9]

can we calculate rolling weeks  and rolling days using As of calender

[hic]

Yes, it's possible and quite straightforward.

However, the As-Of table will grow considerably in size. It will have n*(n+1)/2 records. Hence:

2 years in months: 24*25/2 = 300 records

2 years in weeks: 104*105/2 = 5460 records

2 years in days: 730*731/2 = 266815 records

4 years in days: 1461*1462/2 = 1067991 records

...so I would be careful and not do it over a long period of time

[#11]

Hi Henric this is the way i have written and

my week4rolloing is not working as expected when i selct the value 1, in the flag is not showing the last 4 four weeks, when i selct the week with respect to  Asofweek

pleas ehelp me out

MasterCalendar

(WeekEnd(TempDate)) as week,
TempDate AS Date,

tmpAsOfCalendar:
Load distinct week,Date  Resident MasterCalendar ;

Join (tmpAsOfCalendar)
Load
WEEK as AsofWeek,
Date as Asofdate

  Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month1,week,
     AsofWeek,
     round((week-AsofWeek)*104*105/2) as weekdiff,
     round((Date-Asofdate)*730*731/2) as datediff,
     Resident tmpAsOfCalendar
     Where  AsOfMonth >= Month1;

Drop Table tmpAsOfCalendar;

// ======== Final as of table to get the rolling sums ========

last_table:
load *,
if(weekdiff<4,1,0) as week4rolloing,
if(datediff<7,1,0) as rolling7day

Resident [As-Of Calendar];

drop Table [As-Of Calendar];