topic Re: average calculation excluding 0 value in QlikView

average calculation excluding 0 value

Mon, 04 Aug 2014 15:50:55 GMT

Dear all,

I would like to calculate an average value on a questionnaire exluding the answers with the value 0 because this is linked to a non applicable item.

My quotation is :

CODE NOTATION	GB_SIGNIFICATION
0	Not Applicable to the company
1	Not done, but the subject has been identified
2	Done, but the local procedure has not been documented
3	Done and the local procedure has been documented but is not up-to-date or systematically applied
4	Done and the local procedure has been documented, updated, systematically applied and is familiar to all

How can I manage this ?

thanks in advance

christine

Re: average calculation excluding 0 value

joshabbott — Mon, 04 Aug 2014 15:54:17 GMT

=avg(<CodeNotation-={'0'}> [number you are averaging])

This will act like a syntax error, but will work, if you don't want the syntax error try:

=avg(<CodeNotation={'*'} - {'0'}> [number you are averaging])

Re: average calculation excluding 0 value

Mon, 04 Aug 2014 16:14:02 GMT

Thanks Josh

but I am not sure to well undestand [number you are averaging]

in the exemple below, I would like to obtain a 2.2 resultat ( et not 1.6 )

can you explain more please

best regards

christine

question	ANSWER
A	0
B	2	2
C	4	4
D	3	3
E	0
F	1	1
G	1	1
.	1,6	2,2
.	AVERAGE	WITHOUT 0 VALUES

Re: average calculation excluding 0 value

joshabbott — Mon, 04 Aug 2014 16:17:05 GMT

By putting the <CodeNotation-={0}> in the script, you are using set analysis. In this situation, in your average calculation, you are excluding the 'CodeNotation' of 0 from your average calculation. Hope that helps. If you had 'Avg(Amount), it would give you the first column of answers above. By adding the '<CodeNotation-={0}> that I supplied you, it should give you the second value for each question.

Re: average calculation excluding 0 value

antoniotiman — Mon, 04 Aug 2014 16:48:40 GMT

Hi Christine,

I recommend You to use

Num(Sum(Answer)/Count({<Answer-={0}>} Question),'#.##0,0')

Regards

Re: average calculation excluding 0 value

Tue, 05 Aug 2014 05:53:42 GMT

Hi,

if its not considering 0 you can try sum(Answer) / count(Question)

Re: average calculation excluding 0 value

maxgro — Tue, 05 Aug 2014 07:59:10 GMT

sum(ANSWER) / Count(if(ANSWER>0,ANSWER))