Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

calculation between months(addmonths)?

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:

monthyearsitetotalavg
12/2013A12
1/2014A14
2/2014A35
3/2014A24
4/2014A643
5/2014A564
12/2013B4324
1/2014B5345
2/2014B45
3/2014B434
4/2014B5
5/2014B768
12/2013C67
1/2014C24

expected output:

monthyearsitetotalavgturnover
12/2013A12-
1/2014A1413
2/2014A3524.5
3/2014A2429.5
4/2014A643333.5
5/2014A564603.5
12/2013B43242444
1/2014B53454834.5
2/2014B452695
3/2014B434239.5
4/2014B5219.5
5/2014B768386.5
12/2013C67835
1/2014C2445.5

Thanks.

7 Replies
Not applicable

Re: calculation between months(addmonths)?

Try using rangeavg with above in a straight table

MVP & Luminary
MVP & Luminary

Re: calculation between months(addmonths)?

Hi,

Try below expression

=RangeAvg(Above(totalAvg), totalAvg)

Regards,

Jagan.

sujeetsingh
Honored Contributor III

Re: calculation between months(addmonths)?

Please explain in detail

MVP
MVP

Re: calculation between months(addmonths)?

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;

=======

Not applicable

Re: calculation between months(addmonths)?

hey sorry the expected output is:

monthyearsitetotalavgturnover
(end of) 12/2013A12

6

(end of) 1/2014A1413
(end of) 2/2014A3524.5
(end of) 3/2014A2429.5
(end of) 4/2014A643333.5
(end of) 5/2014A564603.5
(end of) 12/2013B43242162
(end of) 1/2014B53454834.5
(end of) 2/2014B452695
(end of) 3/2014B434239.5
(end of) 4/2014B5219.5
(end of) 5/2014B768386.5
(end of) 12/2013C6733.5
(end of) 1/2014C2445.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.

Not applicable

Re: calculation between months(addmonths)?

sorry it does not work

Not applicable

Re: calculation between months(addmonths)?

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

monthyearsitetotalavgturnover
(end of) 12/2013A12

6

(end of)1/2014A1413
(end of)2/2014A3524.5
(end of)3/2014A2429.5
(end of)4/2014A643333.5
(end of)5/2014A564603.5
(end of)12/2013B43242162
(end of)1/2014B53454834.5
(end of)2/2014B452695
(end of)3/2014B434239.5
(end of)4/2014B5219.5
(end of)5/2014B768386.5
(end of)12/2013C6733.5
(end of)1/2014C2445.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.