Announcements
cancel
Showing results for
Did you mean:
Creator III

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

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

1 Solution

Accepted Solutions
MVP

You can deceive the chart label using Dual function

=Dual(Month(Year_Month), Year_Month)

31 Replies
Champion

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...

Creator III
Author

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)

Champion

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

Creator III
Author

Here is what I need but in 1 chart.

Champion

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

Creator III
Author

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

Champion

Champion

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..

MVP

Will check....

Community Browser