calculation between months(addmonths)?

[#1]

Hi, how can I sum up and divide by 2 the 'totalavg' data for '12/2013 and 1/2014', '1/2014 and 2/2014', etc,...according to site (refer below)? Do I use straight table?

current table:

monthyear	site	totalavg
12/2013	A	12
1/2014	A	14
2/2014	A	35
3/2014	A	24
4/2014	A	643
5/2014	A	564
12/2013	B	4324
1/2014	B	5345
2/2014	B	45
3/2014	B	434
4/2014	B	5
5/2014	B	768
12/2013	C	67
1/2014	C	24

expected output:

monthyear	site	totalavg	turnover
12/2013	A	12	-
1/2014	A	14	13
2/2014	A	35	24.5
3/2014	A	24	29.5
4/2014	A	643	333.5
5/2014	A	564	603.5
12/2013	B	4324	2444
1/2014	B	5345	4834.5
2/2014	B	45	2695
3/2014	B	434	239.5
4/2014	B	5	219.5
5/2014	B	768	386.5
12/2013	C	67	835
1/2014	C	24	45.5

Thanks.

[#2]

Try using rangeavg with above in a straight table

[jagan]

Hi,

Try below expression

=RangeAvg(Above(totalAvg), totalAvg)

Regards,

Jagan.

[sujeetsingh]

Please explain in detail

[MK_QSL]

Like this?

=======

Temp:

Load

  DUAL(Date#(monthyear,'M/YYYY'),Date(Date#(monthyear,'M/YYYY'),'YYYYMM')) as monthyear,

  site,

  totalavg

Inline

[

  monthyear, site, totalavg

  12/2013, A, 12

  1/2014, A, 14

  2/2014, A, 35

  3/2014, A, 24

  4/2014, A, 643

  5/2014, A, 564

  12/2013, B, 4324

  1/2014, B, 5345

  2/2014, B, 45

  3/2014, B, 434

  4/2014, B, 5

  5/2014, B, 768

  12/2013, C, 67

  1/2014, C, 24

];

Final:

Load

  monthyear,

  site,

  totalavg,

  (totalavg + Previous(totalavg))/2 as turnover

Resident Temp

Order By site, monthyear;

Drop Table Temp;

=======

[#6]

hey sorry the expected output is:

monthyear	site	totalavg	turnover
(end of) 12/2013	A	12	6
(end of) 1/2014	A	14	13
(end of) 2/2014	A	35	24.5
(end of) 3/2014	A	24	29.5
(end of) 4/2014	A	643	333.5
(end of) 5/2014	A	564	603.5
(end of) 12/2013	B	4324	2162
(end of) 1/2014	B	5345	4834.5
(end of) 2/2014	B	45	2695
(end of) 3/2014	B	434	239.5
(end of) 4/2014	B	5	219.5
(end of) 5/2014	B	768	386.5
(end of) 12/2013	C	67	33.5
(end of) 1/2014	C	24	45.5

for the first month for every site, which in this case starts from 12/2013, the totalavg should divide by 2 to get turnover. The dates in 'monthyear' is actually the end of each month, so i want to find the turnover for each month, for eg. from end of 12/2013 to end of 1/2014, im calculating the turnover for 1/2014. so i want to do the same for the rest of the months. Thanks.

[#7]

sorry it does not work

[#8]

hi how do i do that? sorry i've changed the expected output:

monthyear	site	totalavg	turnover
(end of) 12/2013	A	12	6
(end of)1/2014	A	14	13
(end of)2/2014	A	35	24.5
(end of)3/2014	A	24	29.5
(end of)4/2014	A	643	333.5
(end of)5/2014	A	564	603.5
(end of)12/2013	B	4324	2162
(end of)1/2014	B	5345	4834.5
(end of)2/2014	B	45	2695
(end of)3/2014	B	434	239.5
(end of)4/2014	B	5	219.5
(end of)5/2014	B	768	386.5
(end of)12/2013	C	67	33.5
(end of)1/2014	C	24	45.5

for the first month for every site, which in this case starts from 12/2013, the totalavg should divide by 2 to get turnover. The dates in 'monthyear' is actually the end of each month, so i want to find the turnover for each month, for eg. from end of 12/2013 to end of 1/2014, im calculating the turnover for 1/2014. so i want to do the same for the rest of the months. Thanks.