Calculating Averages without including NA

[#1]

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.

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem?

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