Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
matthewjbryant
Contributor II

Rolling 3 Month and 12 Month Sales

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Rolling 3 Month and 12 Month Sales

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


talk is cheap, supply exceeds demand
17 Replies
MVP
MVP

Re: Rolling 3 Month and 12 Month Sales

Check enclosed file..

matthewjbryant
Contributor II

Re: Rolling 3 Month and 12 Month Sales

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
Honored Contributor III

Re: Rolling 3 Month and 12 Month Sales

Check attachment.

Thanks,
AS

matthewjbryant
Contributor II

Re: Rolling 3 Month and 12 Month Sales

Hi Amit,

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

Matt

Employee
Employee

Re: Rolling 3 Month and 12 Month Sales

Hi Matt,

would this work ?

Loic

matthewjbryant
Contributor II

Re: Rolling 3 Month and 12 Month Sales

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

MVP & Luminary
MVP & Luminary

Re: Rolling 3 Month and 12 Month Sales

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


talk is cheap, supply exceeds demand
matthewjbryant
Contributor II

Re: Rolling 3 Month and 12 Month Sales

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.

MVP & Luminary
MVP & Luminary

Re: Rolling 3 Month and 12 Month Sales

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.


talk is cheap, supply exceeds demand