Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
ziabobaz
Not applicable

Rolling 3 months problem when crossing YEAR

Hi

This is common question here, I tried the solution discussed here, but it didn't work.

When single YEAR is selected, say 2017, previous year sales are not included in rolling sales.

I use the following formula to compute rolling sum of sales for last 3 months:

rangesum(

above(

  aggr(

  Sum( {< [Month]=,[Year]=>} Sales),

     [Month] ),0,3)  )

It does not work.

Any idea?

Thank you!

Screenshot_60.jpg

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Rolling 3 months problem when crossing YEAR

No in your Aggr() function's dimension

Try this may be

Aggr(RangeSum(Above(Sum({< [Month]=,[Year]=>} Sales), 0, 3)), (MonthYear, (NUMERIC)))

7 Replies
sunny_talwar
Not applicable

Re: Rolling 3 months problem when crossing YEAR

Does your Month include Month and Year or just Month?

ziabobaz
Not applicable

Re: Rolling 3 months problem when crossing YEAR

In this example - just Month as Dimension.

I also tried to make MonthYear as dimension, but it didn't work either

sunny_talwar
Not applicable

Re: Rolling 3 months problem when crossing YEAR

No in your Aggr() function's dimension

Try this may be

Aggr(RangeSum(Above(Sum({< [Month]=,[Year]=>} Sales), 0, 3)), (MonthYear, (NUMERIC)))

ziabobaz
Not applicable

Re: Rolling 3 months problem when crossing YEAR

in my AGGR i tried both Month and MonthYear. Neither did work.

Your formula returns '-' in my table. Did you intentionally omit SUM before Aggr?

Aggr(RangeSum(Above(Sum({< [Month]=,[Year]=>} Sales), 0, 3)), (MonthYear, (NUMERIC)))

sunny_talwar
Not applicable

Re: Rolling 3 months problem when crossing YEAR

Moved the Aggr() to the most outer function, yes... is that what you question was? Can you share a sample to check this out?

ziabobaz
Not applicable

Re: Rolling 3 months problem when crossing YEAR

Yeah, it worked.

I placed the "(" bracket in the wrong place at first. Thank you!

sunny_talwar
Not applicable

Re: Rolling 3 months problem when crossing YEAR

Super... please close the thread if you got what you were looking for.

Best,

Sunny