topic Re: calculation between months(addmonths)? in QlikView

calculation between months(addmonths)?

Wed, 25 Jun 2014 07:43:14 GMT

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.

Re: calculation between months(addmonths)?

Wed, 25 Jun 2014 07:44:42 GMT

Try using rangeavg with above in a straight table

Re: calculation between months(addmonths)?

jagan — Wed, 25 Jun 2014 07:58:04 GMT

Hi,

Try below expression

=RangeAvg(Above(totalAvg), totalAvg)

Regards,

Jagan.

Re: calculation between months(addmonths)?

sujeetsingh — Wed, 25 Jun 2014 07:59:53 GMT

Please explain in detail

Re: calculation between months(addmonths)?

MK_QSL — Wed, 25 Jun 2014 08:00:24 GMT

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;

=======

Re: calculation between months(addmonths)?

Wed, 25 Jun 2014 09:21:37 GMT

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.

Re: calculation between months(addmonths)?

Wed, 25 Jun 2014 09:24:39 GMT

sorry it does not work

Re: calculation between months(addmonths)?

Wed, 25 Jun 2014 09:28:09 GMT

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