Rolling 3 months

[ramanarayan]

Hi Experts,

stalwar1‌

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

[sunny_talwar · Accepted solution]

Try this

Dimension

Month_Name (instead of Month Name)

Expression

Sum(Qty)

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

[ramanarayan]

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

[sunny_talwar]

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)

[ramanarayan]

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

Appreciate your help. Have a gr8 rest of your day

[sunny_talwar]

Thanks buddy and you too!!

[ramanarayan]

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

[sunny_talwar]

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

[ramanarayan]

works as expected. thanks