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

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

    Helene Rao

      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 !







      Answer 1


      Answer 21ABCCorrectA456
      Answer 31ABCB456
      Answer 41ABCC456
      Answer 51DEFCorrect789
      Answer 61DEFCorrect789
      Answer 71DEFCorrect456
      Answer 81ABCCorrectC456
      Answer 91ABCFalseC456
      Answer 101DEFFalseC456