Rolling 3 Month and 12 Month Sales

[matthewjbryant]

Hi All,

Here's a nice little problem: I have some sales data (replicated on the Data tab of the attached) and need to create graphs for the rolling sales up to each month (see Required in QV tab on the attached). I haven't got a clue! Please impart your knowledge!

Matt

[matthewjbryant]

Sorry, the Asof table itself isn't that big really, but joining it to my data creates a monster! I have many rows for each date because each row relates to a transaction.

[lft]

AsOfs tables are indeed the best way to play with Year to date, Rolling period and Date to date cases.

Far better than set analysis, triggers and alternate states IMHO.

They perform well and are rather simple to build compared to the equivalent live SQL queries in other technologies.

Even if you went down at day level, with for instance a 60 rolling days calculation, that would be ~219K rows for 10 years of data. This would run on a low performing laptop without any problems.

Be carefull with joins on a Fact table. Joining an AsOf table to a Fact table will surely result in duplicating data and thus wrong calculation. Generally the AsOf table will be linked to the calendar table or direcly to the Fact table without joins.

Loic

[Gysbert_Wassenaar]

But you don't need to join it. Simply having the two tables associated on the common Month field name is enough.

[matthewjbryant]

That's what I've done, but it still seems to run through all the rows during the LOAD. Qlikview is very good at loading large amounts of data; it just seems like an odd way of doing it to me. It is what it is though! Thanks for the help.

[Gysbert_Wassenaar]

Well, I did use a load distinct in the example and that is costly on large fact tables. It's probably better create a master calendar table first (see for example Tutorial - Using Common Date Dimensions and Shared Calendars.) and generate the AsOf table from that.

[matthewjbryant]

I'm gonna have to seriously think about how to incorporate this into our current model if it's at all possible. We use a 2-tiered approach and so I need to output the report into a QVD. This means that I need to join the tables to make a single table for the QVD, but the QVD is 75MB when nearly all my other QVDs are 1MB! Adding this to the load of the master report just killed my laptop.

Any suggestions?

[lft]

Hi Mat,

As already suggested, I think your problem comes from the JOIN. In the case of dates, you don't really need to perform joins. The master calendar and AsOf tables should be separate tables and QlikView will use the associative engine to link those.

So, would it be possible in your approach to output multiple QVDs instead of a single one ?

If not you could always output a QlikMart (QVW containing multiple tables and no GUI) and perform a binary load on this qvw in your 2nd tier. Binary load is faster than QVD loading, enables you to load multiple tables from only one file but can only be performed once in the script (first instruction only).

If not then ... get a stronger laptop ? A qvd of 75MB should load without any problems on any recent hardware anyway. Or maybe you have advanced expressions with aggr and set analysis ?

Loic

[matthewjbryant]

Hi Loic,

Thanks for the help. While reflecting on your suggestions I realised that if I create the AsOf table from a master calendar in the main QVW after loading the QVDs then the problem should be solved, and it was. I think the problem with loading the 75MG QVD was that it was one of about 30 others. Anyway, problem solved and lots learnt!

Thanks for your help.