topic Rolling Periods in QlikView

Rolling Periods

Karl_Hart — Sat, 16 Nov 2024 04:28:58 GMT

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

Re: Rolling Periods

Gysbert_Wassenaar — Tue, 12 Feb 2019 10:49:38 GMT

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))

Re: Rolling Periods

Karl_Hart — Wed, 13 Feb 2019 07:52:42 GMT

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