topic Re: Rolling Month Sum in QlikView

Rolling Month Sum

aslam24196 — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

I needed rolling sum of current year for 12 months, if there are less than 12 months available, it should take how much ever months are remaining from the previous year column. I did achieve this in a text box for result. In my application, when I click a MonthYear, the text box displays the corresponding Rolling Sum. However, I need this in a straight chart, with MonthYear as Dimension and Rolling month in its corresponding expressions column. I have attached the qvw just in case. There is text object. I need the values of that in the chart. If possible in the backend, please guide me.

Thank you guys.

Re: Rolling Month Sum

sudhirpkuwar — Thu, 26 Apr 2018 06:24:20 GMT

Hi,

Why do you have two separate fields CY and PY....?

Re: Rolling Month Sum

shiveshsingh — Thu, 26 Apr 2018 06:27:11 GMT

Can you elaborate it ?

it should take how much ever months are remaining from the previous year column.

Re: Rolling Month Sum

sudhirpkuwar — Thu, 26 Apr 2018 07:19:43 GMT

You could directly get previous rolling 12 months based on the MonthYear selected using

=Monthstart(Addmonths(MonthYear,-11))

Re: Rolling Month Sum

sudhirpkuwar — Thu, 26 Apr 2018 07:47:09 GMT

I just added another Straight Table with same dim and exp

Its working fine for me

PFA

Re: Rolling Month Sum

aslam24196 — Fri, 27 Apr 2018 11:10:59 GMT

That is the format I got the data in. So for 2016, they have the data only in PY field.

Re: Rolling Month Sum

aslam24196 — Fri, 27 Apr 2018 11:13:53 GMT

If I select the MonthYear Mar 2017, It should calculate the rolling sum for Jan, Feb, Mar 2017 from CY

April to December rolling sum from PY. So that for every month year selected. There is a rolling sum of 12 months.

Re: Rolling Month Sum

sudhirpkuwar — Fri, 27 Apr 2018 11:14:27 GMT

Plz see the above attachment

its working fine....

Re: Rolling Month Sum

aslam24196 — Fri, 27 Apr 2018 11:16:48 GMT

Okay, So instead of the rolling sum displaying the aggregated total, my requirment is to display the rolling so of each monthYear in their corresponding Rows. Not in the totals.

Re: Rolling Month Sum

sudhirpkuwar — Fri, 27 Apr 2018 11:22:08 GMT

You mean like this...?

Re: Rolling Month Sum

aslam24196 — Sat, 28 Apr 2018 06:23:26 GMT

Thanks sudhir for the help. I played around for sometime and I did it. Yes what you did was correct. But it also needs the Rangesum of remaining months prom PY. I'll attach the file..

Thank you,

Cheers!

Re: Rolling Month Sum

aslam24196 — Sat, 28 Apr 2018 06:26:08 GMT

Re: Rolling Month Sum

sudhirpkuwar — Sat, 28 Apr 2018 06:32:39 GMT

Good that you solved it....