topic Re: Sum of Rolling 12 months for each month, 2 lines for selected & previous years in QlikView

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

divya_anand — Fri, 27 Jul 2018 07:35:39 GMT

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

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

YoussefBelloum — Fri, 27 Jul 2018 07:58:08 GMT

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/rangesum.htm

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

divya_anand — Fri, 27 Jul 2018 08:07:46 GMT

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)

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

YoussefBelloum — Fri, 27 Jul 2018 08:14:38 GMT

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

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

divya_anand — Fri, 27 Jul 2018 09:42:04 GMT

Here is what I need but in 1 chart.

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

YoussefBelloum — Fri, 27 Jul 2018 09:49:19 GMT

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

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

divya_anand — Fri, 27 Jul 2018 09:54:59 GMT

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

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

YoussefBelloum — Fri, 27 Jul 2018 09:58:11 GMT

My bad.. I'll use it

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

YoussefBelloum — Fri, 27 Jul 2018 12:25:35 GMT

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

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

sunny_talwar — Fri, 27 Jul 2018 12:35:51 GMT

Will check....

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

YoussefBelloum — Fri, 27 Jul 2018 12:37:47 GMT

thanks

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

sunny_talwar — Fri, 27 Jul 2018 12:58:44 GMT

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

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

sunny_talwar — Fri, 27 Jul 2018 13:01:21 GMT

You can deceive the chart label using Dual function

=Dual(Month(Year_Month), Year_Month)

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

YoussefBelloum — Fri, 27 Jul 2018 13:05:52 GMT

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

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

sunny_talwar — Fri, 27 Jul 2018 13:08:22 GMT

Yes

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

YoussefBelloum — Fri, 27 Jul 2018 13:11:09 GMT

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

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

divya_anand — Fri, 27 Jul 2018 13:19:28 GMT

Hi Sunny,

That's fantastic, thank you.

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

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

sunny_talwar — Fri, 27 Jul 2018 13:20:53 GMT

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

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

divya_anand — Fri, 27 Jul 2018 13:22:08 GMT

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

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

sunny_talwar — Fri, 27 Jul 2018 13:26:49 GMT

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