Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Rolling 12 months

Hi,

I having some problem in rolling 12 months, means here each and every month should have sum of preceding 12 months value.

For example July 2015 to June 2016, July 2015 value should be preceding 12 months and Aug 2015 also should have preceding 12 months.

Can any one help me here?

7 Replies
Highlighted
qlikviewnovice
Valued Contributor II

Re: Rolling 12 months

Hi,

Try with this expression and modify your fields accordingly

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -11))<=$(=Date(Max(Date)))’}>} Sales )

Hope it helps!!

MVP
MVP

Re: Rolling 12 months

May be like this:

RangeSum(Above(Sum(Sales), 0, 12))

You might have to change Above to Below based on your sorting.

Not applicable

Re: Rolling 12 months

Hi,

Thanks for you guys response,

But my point not from July 2015 to June 2016  range sum and only rolling 12 months.

Actually rolling 12 month should be like this which I have mentioned below.

July 2015 = sum of Aug 2014 to July 2015

Aug 2015 =  sum of Sep 2014 to Aug 2015

            .

            .

June 2016= sum of July 2015 to June 2016.

But Format should be July 2015 to June 2016.

MVP
MVP

Re: Rolling 12 months

Format should be July 2015 to June 2016?? I am not sure I understand, can you may be provide a sample with the expected output?

Not applicable

Re: Rolling 12 months

Rolling 12 months for each month.png

I have wrote the formula for this

rangesum(above(sum({<CalendarMonthName=,FiscalYear={'$(=max(FiscalYear))'}>}[Hours_12Mths]),0,12))

But I Want like above diagram.

MVP
MVP

Re: Rolling 12 months

Try this:

RangeSum(Above(Sum({1} [Hours_12Mths]), 0, 12)) * Avg({<CalendarMonthName=,FiscalYear={'$(=max(FiscalYear))'}>} 1)

Not applicable

Re: Rolling 12 months

Hi Sunny,

Thanks for your reply, I have used your expression even though, its not working.

If any one knows, please give me proper solution for this requirement