Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Karl_Hart
Contributor

Rolling Periods

All,

I've been searching for an answer to this but I'm having no luck. I want to show the sales over the last 12 months, so for Jan-19 I want it to show the sum sales from Feb-18 to Jan-19. This in itself is easy enough to do using RangeSum(Above(Sum(Sales),0,12)) for example, but the issue I'm having is I want to restrict the chart to only display 2019, but I still want to keep the data from 2018 within the calculations. When I select 2019, Jan-19 only shows that months and nothing from 2018

I know there must be a way to do this otherwise every 12 months of any such graph will have incomplete data. Are you able to help?

 

Please see attached

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Rolling Periods

Since you've put your Year listbox in the Calendar alternate state you need this expression:

=sum({<Year=Calendar::Year>}aggr(RangeSum(Above(Sum({<MonthYear>}Sales),0,12)),MonthYear))

 


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
MVP & Luminary
MVP & Luminary

Re: Rolling Periods

Since you've put your Year listbox in the Calendar alternate state you need this expression:

=sum({<Year=Calendar::Year>}aggr(RangeSum(Above(Sum({<MonthYear>}Sales),0,12)),MonthYear))

 


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Karl_Hart
Contributor

Re: Rolling Periods

Many thanks @Gysbert_Wassenaar that has worked. I only put the alternate state in this example to explain what I was looking for but I've tweaked your answer to be 

=sum({<Year={2019}>}aggr(RangeSum(Above(Sum({<MonthYear=, Year=>}Sales),0,12)),MonthYear))

and this does the job perfectly