Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

average calculation excluding 0 value

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 NOTATIONGB_SIGNIFICATION
0Not Applicable to the company
1Not done, but the subject has been identified
2Done, but the local procedure has not been documented
3Done and the local procedure has been documented but is not up-to-date or systematically applied
4Done and the local procedure has been documented, updated, systematically applied and is familiar to all

How can I manage this ?

thanks in advance

christine

6 Replies
joshabbott
Creator III
Creator III

=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])

Not applicable
Author

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

questionANSWER
A0
B22
C44
D33
E0
F11
G11
. 1,6   2,2
.AVERAGEWITHOUT 0 VALUES
joshabbott
Creator III
Creator III

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.

antoniotiman
Master III
Master III

Hi Christine,

I recommend You to use

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

Regards

Not applicable
Author

Hi,

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

maxgro
MVP
MVP

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