Pivot table Average calculation

ramyasaiqv — Sat, 02 Sep 2017 20:21:22 GMT

Hi All,

I’m trying to add and average 3 columns in a pivot table and having issue and I need some assistance in resolving my issue.

I have 3 expressions exp1, exp2 and exp3 and 4^th expression is Total Average of the 3 columns (exp1+exp2 + exp3/3).

If all the 3 expressions have data the “Total average” is calculated correct. If any of the expression is null, or ‘ – ‘ then total average is calculated as ‘-‘.

But what I want is if one of the value is null or ‘ –‘ it should ignore that value and average the available values.

For example in the attached qvf and excel Q2-2016 data for TXT and MXT is ‘-‘but RXT Q2-2016 the value for Item ‘ KLS’ is 3.30 and the Total average is calculated as ‘-‘. But the expected result is 3.30 because there is only one value available.

Eg2: For Item ‘XLS’ 2017-Q2 (MXT) is ‘-‘ and TXT and RXT has 4.0 and 2.0 respectively . So the result should be (4+2)/2=3.0 instead of ‘-‘.

Please help me in resolving this issue.

I have attached the qvf and excel with sample data for your reference. Thank you,

Ramya

Re: Pivot table Average calculation

sunny_talwar — Sat, 02 Sep 2017 22:50:16 GMT

Try this

RangeAvg(Column(1), Column(2), Column(3))

Re: Pivot table Average calculation

ramyasaiqv — Sat, 02 Sep 2017 23:49:02 GMT

Thank you so much for the help Sunny.

Regards,

Ramya

topic Re: Pivot table Average calculation in QlikView

Pivot table Average calculation

Re: Pivot table Average calculation

Re: Pivot table Average calculation