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]

Hi,

you will need to use Range functions, like this:

Rangesum(above(total sum(your_measure),0,12))

you can refer to the help to get familiar with the syntax

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/RangeFunctions/rang...

[divya_anand]

Hi Youssef,

I've tried it and it works for the selected period only. This expression works when the dimension is Year_Month which makes sense. But how can I get another line in the same chart for the year previous to the selected year? so, I will want it to work on Month dimension & not Year_Month. Maybe I should take another approach.

Any thoughts?

Rangesum(Above(sum({$<Year=, Month=, Week=,NumDate=}>} Number),0,12))*Avg(1)

[YoussefBelloum]

can you please describe the expected ouput via a screen shot... I can't follow you on this one

[divya_anand]

Here is what I need but in 1 chart.

[YoussefBelloum]

ok it is clear now.. would you be able to share sample data ? need to make some tests in the expression

[divya_anand]

I have attached the data in my question above. You can use the same. That is my base data.

[YoussefBelloum]

My bad.. I'll use it

[YoussefBelloum]

Hi stalwar1‌

I'm really stuck on this one.

I tried using front end expression and AsOfTable... the problem that OP wants cumulative values (2 lines,1 line for every year, using month dimension: from 1 to 12)

after several tries, I wonder if it make sense technically..

[sunny_talwar]

Will check....