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

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)

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