5 Replies Latest reply: Feb 23, 2017 3:44 AM by Helene Rao

# Aggregating (Averaging) multiple dimensions and removing 0/Null values from the average

I am trying to reproduce calculations currently on a spreadsheet into our QlikSense Dashboard

I have a spreadsheet with a number of Survey Questions as Dimensions (Columns) and Survey Answers as Values (rows)

1. For each question there is a correct answer and I know those values as hardcoded strings. eg the correct answer to Q1 is ABC, the correct answer to Q2 is "Correct", the correct answer to Q3 is C, etc
2. Depending on my selection in Qlik, some answers will have no values (eg if I have a filter that selects only Answer 3 and 4, then I have no values for Q2, similarly I have no values for Q3 if my filter singles out answers 5,6,7
3. In excel we define a KPI as such and this is what I am having trouble calculating in Qlik
• We first define the % of correct answers for each question.
• Assuming  ABC is correct answer for Q1,  Q1 % Correct = 6/10 = 60%
• Assuming "Correct" is the answer for Q2, Q2 % Correct = 6/8 = 75%
• Assuming C is the answer for Q3, Q3 % Correct = 4/7 = 57%
• We then calculate the KPI which is the average of those 3 above values, (60+75+57)/3 = 64% using an "averageif" function to exclude any 0% or Null values.
4. In Qlik, I am able to define the above 3  individual KPis so that they react to selection criteria I have and compute the correct %  as such:
1. Q1% Correct: sum({<Q1={"ABC"}*Q1>} SurveyCounter )/count(Total Q1)
2. Q2% Correct: sum({<Q2={"Correct"}*Q2>} SurveyCounter )/count(Total Q2)
3. ... etc
5. Another the example selection criteria singles out the Answers 3 and 4. In that case my calculated  correct % become: 100% for Q1, "-" for Q2, 50% for Q3 which is what I am looking for.
6. But I cant get to the next step, calculating the average of those. I can't seem to reference these calculated KPIs in other function, nor do I know how to average them removing any null values or 0% values.
7. HOW do I  now create the KPI expression that can average all of these together as per the averageif(QuestionsRange, <>0) functionality? With the same selection criteria as above, I would want a KPI to return 75% , ie (100+50)/2, excluding the 0/- % for Q2

Any help very very welcomed !

Helene

Survey

Survey

Counter

Q1Q2Q3...Q7

1

ABCCorrectA123

• ###### Re: Aggregating (Averaging) multiple dimensions and removing 0/Null values from the average

Dear Vineeth

Thank you very much for your help and advice. I want to make sure I have understood your suggestion properly.

Are you suggesting that I should load a separate table that will have each Survey/Question combination as a row ?

...

etc

As I have a number of dashboards preset using all the question as dimensions, I would need to set up this new cross table completely independently for the rest of my data set.

Have I understood this correctly? Thanks Helene

• ###### Re: Aggregating (Averaging) multiple dimensions and removing 0/Null values from the average

Sorry, I do not know how your existing data model is but; CrossTabbing the data will definitely simplify your calculations

Instead of adding individual questions as dimension you can simply drag the Question column and restrict using set analysis or calculated dimensions

• ###### Re: Aggregating (Averaging) multiple dimensions and removing 0/Null values from the average

Hi,

Use CrossTable() to transform the data and validate the answer and create a flag whether it is correct or not then use this flag in set analysis to get the results.

Regards,

jagan.

• ###### Re: Aggregating (Averaging) multiple dimensions and removing 0/Null values from the average

Dear Jagan and Vineeth

Thank you very much for both your suggestions. I now have created a cross table and my data looks like this:

Answer 1 | Q1 |  ABC | Y

Answer 1 | Q2 | Correct | Y

Answer 1 | Q3 | A | N

Answer 2 | Q1 | ABC | Y

Answer 2 | Q2 |Correct | Y

Answer 2 | Q3 | A | N

....

Answer 7 | Q1 | DEF | N

Answer 7 | Q2 | Correct | Y

Answer 8 | Q1 | ABC | Y

Answer 8 | Q2 | Correct | Y

Answer 8 | Q3 | C | Y

.. etc

In my previous example, the number, the number i was after is the average of those 3 %

• Q1 % Correct = 6/10 = 60%
• Q2 % Correct = 6/8 = 75%
• Q3 % Correct = 4/7 = 57.14%

So (60+75+57)/3 which is not the same as (6+6+4)/(10+8+7).

I can using the set restrictions to calculate each individual % but I am still unclear how to get a total average as per my definition above using a formula than would span the whole cross table.

Don't I need to still need to store each individual % Correct for Q1, Q2, and Q3 and then average them in a two step process ? Maybe i am confused.

Thanks H