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

[MK_QSL]

Check enclosed file..

[matthewjbryant]

Thanks Manish,

I didn't know about the Above and Below functions. They're gonna be really useful!

Is there any way you can remove the blank space created by the NULLs on the graph?

Matt

[amit_saini]

Check attachment.

Thanks,
AS

[matthewjbryant]

Hi Amit,

I've seen this as an answer to another question on the forums. It's not what I'm after.

Matt

[lft]

Hi Matt,

would this work ?

Loic

[matthewjbryant]

Hi Loic,

That's a really interesting approach! I would like to do it with the LOAD I have if I can, but will consider this after exhausting that approach. Thanks for the help - I'm still reading though and learning from this one!

Matt

[Gysbert_Wassenaar · Accepted solution]

Try using an AsOf table like explained in this document: Calculating rolling n-period totals, averages or other aggregations. See attached example.

[matthewjbryant]

Thanks for this. It's a little disappointing that this is so complicated in Qlikview, but from the discussion surrounding this topic it looks like this is the best way. Only problem is that Asof table can be huge! I'm using 4 years of data for the UK side of our company and it's pushing on 10 million lines.

[Gysbert_Wassenaar]

Not really. A year only has 12 months. How many years data do you have? A hundred? That'd be 1200 months. Now, for each month a 12-month rolling period will give you at most 14400 records. For additional 3-month rolling periods another 3600 records. That's peanuts. Only if you want to do rolling periods of N-days with decades of data will you see serious numbers of records in the AsOf table.