4 Replies Latest reply on Oct 8, 2018 12:45 AM by Elaine Fang

# A problem of calculating average data on quarterly and yearly

Can someone help me to solve this problem?

Data：Year，Quarter，Period（Month），Shipment

I need to calculate row named R for each month, quarter and year.

monthly R: WINDOW_SUM(SUM([Shipment]),-2,0)   /sum two previous shipment to the current shipment.

quarterly R:  AVERAGE monthly R.  for example: 2014Q1 R=(7976+18098+13154)/3

yearly R: AVERAGE quarterly R. for example: 2014 R= (2014Q1 R+2014Q2 R+2014Q3 R+2014Q4 R)/4

I have no idea with Quarterly R and Yearly R. Could you help? Thank you.

show quarterly R

show yearly R

• ###### 1. Re: A problem of calculating average data on quarterly and yearly

Could you please attach sample data as twbx format.

Tanks,

Shin

• ###### 3. Re: A problem of calculating average data on quarterly and yearly

Hi Elaine,

Please refer to the attached workbook. Hope it will solve your problem.

• ###### 4. Re: A problem of calculating average data on quarterly and yearly

Hi Seraj,

I thought I don't get the correct answer on Quarterly and Yearly data. It should be shown monthly average and quarterly average, but  not use WINDOW_SUM(SUM([Shipment]),-2,0). Thank you.

monthly R: WINDOW_SUM(SUM([Shipment]),-2,0)   /sum two previous shipment to the current shipment.

quarterly R:  AVERAGE monthly R.  for example: 2014Q1 R=(7976+18098+13154)/3

yearly R: AVERAGE quarterly R. for example: 2014 R= (2014Q1 R+2014Q2 R+2014Q3 R+2014Q4 R)/4