
1. Re: Average of a Moving Sum ( Table Calculation)
Tharushiw Fernando Jul 29, 2016 10:38 AM (in response to Tharushiw Fernando)Hi did anyone figure this out yet?

2. Re: Average of a Moving Sum ( Table Calculation)
John Sobczak Jul 29, 2016 10:56 AM (in response to Tharushiw Fernando)Is this what you need?

3. Re: Average of a Moving Sum ( Table Calculation)
Tharushiw Fernando Jul 29, 2016 11:39 AM (in response to John Sobczak)Hi John,
I was able to calculate the moving sum and the results of that is what I have pasted on the table above.
I need to calculate one column which is the average column ( in red) that gives me :
e.g. sum (Jan  Dec moving sum of sales) / number of months
Appreciate your help!

4. Re: Average of a Moving Sum ( Table Calculation)
Tharushiw Fernando Jul 29, 2016 12:15 PM (in response to Tharushiw Fernando)Hi John!
That's the format yes, but the numbers are different from I am expecting.
I calculated the avg by sum (moving sum for each year) divided by number of months.
Note: 12 doesn't have data for JanNov since there's not enough data to calculate the 12 month moving sum for those months.
Year Avg 2012 569,651 2013 1,077,138 2014 1,082,432 2015 1,099,078 2016 1,054,929 Please help to figure out how to get the above average numbers for each year.
Thank You!!!

5. Re: Average of a Moving Sum ( Table Calculation)
Tharushiw Fernando Aug 1, 2016 8:19 AM (in response to Tharushiw Fernando)Morning Folks!
Was anyone able to figure out how to calculate the average column mentioned above?
Thanks!
Tharushi

6. Re: Average of a Moving Sum ( Table Calculation)
John Sobczak Aug 1, 2016 9:18 AM (in response to Tharushiw Fernando)I have your results in the attached workbook.

7. Re: Average of a Moving Sum ( Table Calculation)
Tharushiw Fernando Aug 1, 2016 3:15 PM (in response to John Sobczak)Wow thanks John!
However I am unable to figure out how you got that. When I try to recreate the calculation that you have "if last()= 0 then [Window Average of Moving Sum] end" i get different answers.
Apologies for the back and forth, appreciate your time and help on this.
Regards,
Tharushi

8. Re: Average of a Moving Sum ( Table Calculation)
John Sobczak Aug 2, 2016 3:31 AM (in response to Tharushiw Fernando)2 of 2 people found this helpfulIt's what I call the Rubik's cube of Table Calc settings. There are actually a few nested table calcs inside this one in which there are settings to address and partition for each. See images below:

9. Re: Average of a Moving Sum ( Table Calculation)
Tharushiw Fernando Aug 2, 2016 9:44 AM (in response to John Sobczak)John,
This is perfect. Thank you for helping out. I learned a lot of new things in the process as well ( Nested Table Calculations!)
Best Regards,
Tharushi