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]

First create your fiscal calendar. Then create the As-Of table based on a field from your new fiscal calendar. If you look at the example table then you'd use the RMonth field for generating the As-Of table.

[jpbartolomeo]

Thanks Gysbert! I will try.

Thanks again!

[#4]

Great explanation Henric,

Really helped a lot.

Thank you very much.

[agni_gold]

If i want to link my current month (As of Month) to only last 3 months data , then what will i do ?

[hic]

If you use the above solution and {$<MonthDiff={"<3"}>} as set expression you will get exactly that.

An alternative is to limit it already when you create the As-Of table. Just change the where-clause to

Where AsOfMonth >= Month and AsOfMonth < AddMonths(Month,3);

HIC

[agni_gold]

Hi Heneric  ,

I have one situation like , when i select any year the, all previous years yearMonth should display , and if i select any year and month then it should show all previous year months.

[zahidrahim_ocp]

Dear Henric,

What changes we need to perform if i want to keep As of "Date" rather than a "Month'? Like all previous data from the date selected for example: 05-Oct-2015 OR 07-Oct-2015.

[hic]

If you use dates instead of months, the As-Of table will be much larger. So don't do it for very large time spans. You can use the following as template (if you already have a master calendar with dates):

// ======== Create a list of Dates ========
tmpAsOfCalendar:
Load distinct Date
Resident [Master Calendar] ;

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Date as AsOfDate
Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Date,
AsOfDate,
AsOfDate - Date as DateDiff,
Round((MonthStart(AsOfDate)-MonthStart(Date))*12/365.2425) as MonthDiff,
Year(AsOfDate)-Year(Date) as YearDiff
Resident tmpAsOfCalendar
Where AsOfDate >= Date;
Drop Table tmpAsOfCalendar;

[zahidrahim_ocp]

Dear Henric,

Thank you for the response, But what is the best solution to generate As of Date reports for large data. As using set expression i am unable to select individual dimensions if they don't have transaction on the selected date in a filter and most of my reports are based on "As of Date".

Regards,

Zahid Rahim

[hic]

You should use something along the lines the script in the answer above.

All I am saying is that you should think twice about the number of dates you use. For instance, if you have one year of data, and one year of as-of dates, you will have ~ 70000 records in the as-of table. Which could be perfectly acceptable.

But if you have four years of data and four years of as-of dates, you will 16 times as many combinations. You will get an as-of table with over 1 million records, and this will most likely affect performance.

HIC