Rolling 3 months problem when crossing YEAR

[ziabobaz]

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!

[sunny_talwar]

Does your Month include Month and Year or just Month?

[ziabobaz]

In this example - just Month as Dimension.

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

[sunny_talwar · Accepted solution]

No in your Aggr() function's dimension

Try this may be

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

[ziabobaz]

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]

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]

Yeah, it worked.

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

[sunny_talwar]

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

Best,

Sunny