Calculating Averages without including NA

Fri, 31 Jul 2015 01:51:28 GMT

Currently i have this table

As you can see, my dimensions are Questions and Rotation.

The set analysis below is my expression.

=Avg({<[Rotation],Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7','Q8','Q9','Q10','Q11','Q12','Q13','Q14','Q15','Q16','Q17','Q18','Q19','Q20','Q21','Q22','Q23','Q24','Q25','Q26'}>}Aggr(((SUM(Data)/

COUNT({<Data-={"NA"}>}Rotation)))

, Questions,Rotation))

By checking the box to show partial sums for both dimensions, i will get "Rotation on Avg Score" and the last row, as the Program Average.

Unfortunately, the result is not exactly how i want it. NA represents empty and this calculation takes an average of all including NA.

For example, Q9's program average should be 5.13 and not 1.28.

I understand that the problem lies with the set analysis where i declare Questions={'Q1',........,'Q26'} .

I was looking into dynamic set analysis which could work but need some guidance.

Re: Calculating Averages without including NA

Gysbert_Wassenaar — Fri, 31 Jul 2015 05:13:45 GMT

Can you post a qlikview document that demonstrates the problem?

You can use the scramble feature to scramble any confidential information.

topic Calculating Averages without including NA in QlikView

Calculating Averages without including NA

Re: Calculating Averages without including NA