topic Re: Rolling 3 months in QlikView

Rolling 3 months

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

Hi Experts,

In my attached qv file , i have loaded 2 files and joined them with a Key as per my present DB scenario.

Now my requirement is simple...

In my 2nd Expression(Line Diagram), i have used Rangesum and Below functions to get the rolling 3 months value..However, For month of May'17 & Jun'17.. it calculates for 2 months and 1 month respectively (I understand this calculation happens due to the dimension that i have used).. But i do have values (Fcst_Qty) till Month of Dec'17 in other table.. Can we still calculate the Fcst_Qty (rolling 3 months) for May'17 and Jun'17 (Till Apr'17, rolling 3 months numbers are perfect)??

Right now in my DB, for May it is 24, Jun it is 23.. But the right number should be 46 for May'17 and 112 for Jun'17

Thanks!!

Re: Rolling 3 months

sunny_talwar — Wed, 13 Jun 2018 11:04:11 GMT

Try this

Dimension

Month_Name (instead of Month Name)

Expression

Sum(Qty)

If(Sum(Qty) <> 0, RangeSum(Below(sum(Fcst_Qty),0,3)))

Re: Rolling 3 months

ramanarayan — Wed, 13 Jun 2018 11:08:43 GMT

HI Sunny,

Appreciate the reply. This is possibly a solution where you changed the dimension tweaked the expression. Is there a way to acheive this without changing the dimension?

Thanks,

Ram

Re: Rolling 3 months

sunny_talwar — Wed, 13 Jun 2018 11:11:10 GMT

I don't believe so... unless you make changes in the script... your table looks like this right now

Future Fcst_Qty are associated with null Month Name... either fix that in the script or use Month_Name (which is already fixed)

Re: Rolling 3 months

ramanarayan — Wed, 13 Jun 2018 11:14:23 GMT

Thanks. Possibly the right approach is to change the dimension.

Appreciate your help. Have a gr8 rest of your day

Re: Rolling 3 months

sunny_talwar — Wed, 13 Jun 2018 11:16:17 GMT

Thanks buddy and you too!!

Re: Rolling 3 months

ramanarayan — Wed, 13 Jun 2018 14:10:55 GMT

Hi stalwar1,

Another strange issue. I tried adding one more to platform name to the earlier data (Platform name as "DEF").. Now when I try selecting a platform name, numbers for May and Jun is not calculated for rolling 3 months.. Attached is the QV file and source files..

Re: Rolling 3 months

sunny_talwar — Wed, 13 Jun 2018 14:13:04 GMT

Not sure why you have duplicates, but if you select in Platform_Name instead of Platform Name, it works, right?

Re: Rolling 3 months

ramanarayan — Wed, 13 Jun 2018 14:17:49 GMT

works as expected. thanks