
Re: Using rangesum and above and below to calculate 12 months rolling total in graphs
Sunny Talwar Feb 29, 2016 3:06 PM (in response to thierry geufroi)Totally forgot about responding to the other post. What is the output you are expecting? Can you put that in an Excel file?


Re: Using rangesum and above and below to calculate 12 months rolling total in graphs
thierry geufroi Mar 1, 2016 4:23 AM (in response to Sunny Talwar )Sunny, almost there.
thanks again for your support. (We are not in the same time zone, so I come to you with a bit of delay).
I changed your formula for MAT LY just a little bit
=If(RowNo() >23 , RangeSum(Above(Sum({<Quarter, Month, Period, PeriodID, Year>}Units), 12, 12)))
rather than
=If(RowNo() >23 , RangeSum(Above(Sum({<Quarter, Month, Period, PeriodID, Year>}Sales), 13, 12))).
to make it short you saved me again.
otherwise the graph doesn't adapt with the selected year...will that be hard to include in the formula ?
if 2014 is selected the max period for MAT will be 201412 and the max corresponding period LY will be 201312.
this is no big deal !

Re: Using rangesum and above and below to calculate 12 months rolling total in graphs
thierry geufroi Mar 1, 2016 4:58 AM (in response to thierry geufroi)Actually, I have another concerned
if a product has no sales in the LY but only in the current year, then no graph is ploted at all > no data to display.
this should be something to fix via set analysis.
The more difficult issue was the MAT that you did manage, Thanks.
can't wait to see your feedback.
thierry

Re: Using rangesum and above and below to calculate 12 months rolling total in graphs
Sunny Talwar Mar 1, 2016 6:08 AM (in response to thierry geufroi)Would you be able to point out a selection where the product had no sales LY, but has it in current year?

Re: Using rangesum and above and below to calculate 12 months rolling total in graphs
thierry geufroi Mar 1, 2016 6:56 AM (in response to Sunny Talwar )Sunny,
uploaded a more complete version version (sampleQVMAT).
you will see productsTAR, TOPLA, TOMIX for wich the problem occurs...

Re: Using rangesum and above and below to calculate 12 months rolling total in graphs
Sunny Talwar Mar 1, 2016 8:52 AM (in response to thierry geufroi)Checkin it






Re: Using rangesum and above and below to calculate 12 months rolling total in graphs
Sunny Talwar Mar 1, 2016 1:26 PM (in response to thierry geufroi)I have put a condition so that you don't compare a 111 month number to a 12 month number. If I remove the conditions (RowNo > 23) you will see something like this:
As these circled part were still accumulating. Now you won't have problem seeing the data for VAR or TOPLA
But my thinking is that this TOPLA alone might look okay, but others are not very insightful. Have a look.

SAMPLEQVMAT.qvw 187.0 K

Re: Using rangesum and above and below to calculate 12 months rolling total in graphs
thierry geufroi Mar 1, 2016 4:10 PM (in response to Sunny Talwar )Thanks stalwar1.
I had to sort the period by text ascending in order not to have periods all mixing.
for me it's acceptable to have first 12 months cumulative sales increasing before the first complete MAT.
Which is important is that for the minimum 12 last periods the comparison is feasible and working fine. !
so again thanks !!!

Re: Using rangesum and above and below to calculate 12 months rolling total in graphs
Sunny Talwar Mar 1, 2016 4:28 PM (in response to thierry geufroi)So all is working now? We are good to close the thread?

