Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER

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

91 Comments
Specialist
Specialist

Does anyone have a suggestion for how to handle multiple months filter confusion with end users.  What I mean is that my user selects the month field from the master calendar to filter the dashboard but this breaks the chart using the asofmonth field.  The user then has to remove the month filter applied through the master calendar and apply the asofmonth filter to filter the chart using the asofmonth field.

0 Likes
557 Views
Specialist III
Specialist III


@mgranillo 

Have you tried either

  • using a {<Month = , >} in the set analysis for the measures referencing the Asofmonth field
  • Or using Alternate states
0 Likes
549 Views
Contributor
Contributor

Hi Henric,

Thanks for this great article! It is very useful!

 

I have a question please: I have a graphic where I want to display the 12 previous months according to the selected YearMonth (YMAO field) --> there is only one selected YMAO value.

I'm using this formula:

Sum({<MonthDiff={">=0<12"}>} Sales)

it shows only one point (for selected YMAO) with the total sales for 12 months. This total is correct, but I want to display the other months in the graphic in order to have sales by each month. I tried with this formula, but I get the same result as before:

Sum(Aggr(Sum({<MonthDiff={">=0<12"}>} Sales), YMAO))

 

Do you have any suggestion?

Thanks in Advance,

Elinn

0 Likes
484 Views
Specialist III
Specialist III

Hi @elinn08 

 

Do you mean something like this?

 

Line Chart.JPG

467 Views
Contributor
Contributor

Hi Robert99,

Yes, something like that

I added to the graphic the date field that links the as-of table with the calendar table and it seems to work

If you have another suggestion, please let me know

Thanks,

Elinn

462 Views
Specialist III
Specialist III

@elinn08 

How did you do it using AsofTable? I would prefer to do it this way if possible.

I did this using Canonical Dates. 

The client wanted to show the last 15-18 MonthYears with the prev 2 years showing. I eventually did this using the Canonical date approach (with the CanonDate joined to the calendar).

And CanonType used in the measure to determine the curr vs previous years

DATEBridge:
Load
LINK_FactTable ,
DocPostingDate AS DateCanon,
'INVOICE' as CanonType //This si both invoiced and shipped. Its for all dates. Should be CurrYear
resident FACT_TABLES
// where DataType = 'InvOrCredits'
;
 
DATEBridge: //prev year
Concatenate (DATEBridge)
Load
LINK_FactTable ,
addmonths(DocPostingDate,+12) AS DateCanon,
'InvPastYear1' as CanonType
//InvoiceSalesNet as InvSalesNetPrevYr
resident FACT_TABLES
;

DATEBridge: //prev year *2
Concatenate (DATEBridge)
Load
LINK_FactTable ,
addmonths(DocPostingDate,+24) AS DateCanon,
'InvPastYear2' as CanonType
// InvoiceSalesNet as InvSalesNetPrevYr2
resident FACT_TABLES
;

0 Likes
456 Views