9 Replies Latest reply on Aug 2, 2016 9:44 AM by Tharushiw Fernando

Average of a Moving Sum ( Table Calculation)

Hi,

I need to calculate the overall average of a 12 month moving sum.

Please see attached data that I have added in an excel doc.

I was able to calculate the moving sum for the last 12 months using a table calculation for each month

• WINDOW_SUM(SUM([SALES]), -11, 0)

I need to get an average for each year.

The table below shows the Moving Sum for each month (last 12 months including current) >>> I need HELP with calculating the average column ( either in the same worksheet or separate)

Year/Month
JanFebMarApr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Average
201210664481066448
20131076993108286910851921088989107459510840031079976107890310693311067264106568210718591077138
20141071634106734710651851065114107792510792531073949107294610891521100414111066811155791082430
20151118817112312911228361118798111893211053631100130109853110878241075567106624610527481099076
201610495921055429105775510628711061556107357310237251054929

* Is there a better way to get the moving sum for each month, year using an LOD calculation?

Appreciate the Help.

Thanks!

• 1. Re: Average of a Moving Sum ( Table Calculation)

Hi did anyone figure this out yet?

• 2. Re: Average of a Moving Sum ( Table Calculation)

Is this what you need?

• 3. Re: Average of a Moving Sum ( Table Calculation)

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

• 4. Re: Average of a Moving Sum ( Table Calculation)

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 Jan-Nov 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

Thank You!!!

• 5. Re: Average of a Moving Sum ( Table Calculation)

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)

I have your results in the attached workbook.

• 7. Re: Average of a Moving Sum ( Table Calculation)

Wow thanks John!

However I am unable to figure out how you got that. When I try to re-create 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)

It'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:

2 of 2 people found this helpful
• 9. Re: Average of a Moving Sum ( Table Calculation)

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