Sum of Rolling 12 months for each month, 2 lines for selected & previous years

[divya_anand]

Hello,

I am trying to create a line chart with:

1 line: rolling 12 months for selected year & month

2 line: rolling 12 months for the year previous to selected year

& when I say rolling, the value for each month is an accumulation of previous 12 months. Col C in the below picture is what I would like to see on the chart when I select Year=2018 & Month=Feb.

As mentioned, when I make the above selection I should have

1 line: feb 2018 to mar 2017

2 line: feb 2017 to mar 2016

    

[YoussefBelloum]

thanks

[sunny_talwar]

Can be done with The As-Of Table like you mentioned... but here is one option without using As Of Table

[sunny_talwar · Accepted solution]

You can deceive the chart label using Dual function

=Dual(Month(Year_Month), Year_Month)

[YoussefBelloum]

I see.. the trick behind Above(rangesum(above(... is that it rollback 24 months ?

[sunny_talwar]

Yes

[YoussefBelloum]

a bit complicated to understand.. was trying the same approach creating a field with autonumber(year_month) and using it inside a range set analysis ( >=max()-24 <=max() )  inside the sum.. didn't work..

[divya_anand]

Hi Sunny,

That's fantastic, thank you.

Do you have an idea as to why the months are showing up as below?

[sunny_talwar]

Is this how you see it when you open my attached file?

[divya_anand]

No, that's what I see when I implement the same in the original report that I am working on.

[sunny_talwar]

Well, I have no idea what you have... can you share a sample?