Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
matthewjbryant
Creator II
Creator 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
Gysbert_Wassenaar

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

View solution in original post

17 Replies
MK_QSL
MVP
MVP

Check enclosed file..

matthewjbryant
Creator II
Creator II
Author

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
Master III
Master III

Check attachment.

Thanks,
AS

matthewjbryant
Creator II
Creator II
Author

Hi Amit,

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

Matt

lft
Employee
Employee

Hi Matt,

would this work ?

Loic

matthewjbryant
Creator II
Creator II
Author

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

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
Creator II
Creator II
Author

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.


talk is cheap, supply exceeds demand