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

[#1]

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 Answer	Survey Counter	Q1	Q2	Q3	...	Q7
Answer 1	1	ABC	Correct	A		123
Answer 2	1	ABC	Correct	A		456
Answer 3	1	ABC		B		456
Answer 4	1	ABC		C		456
Answer 5	1	DEF	Correct			789
Answer 6	1	DEF	Correct			789
Answer 7	1	DEF	Correct			456
Answer 8	1	ABC	Correct	C		456
Answer 9	1	ABC	False	C		456
Answer 10	1	DEF	False	C		456

[vinieme12]

use a crosstable load

The Crosstable Load

[#3 · Accepted solution]

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 ?

Answer 1  Q1  ABC

Answer 1  Q2  Correct

Answer 1  Q3  A

...

Answer 2  Q1 ABC

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

[jagan]

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.

[vinieme12]

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

[#6]

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:

SurveyAnswer | Question | Answer | CorrectYorN

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