2 Replies Latest reply: Nov 8, 2016 5:57 PM by Yolanda Wei RSS

    Qlikivew Bar chart/ Table: Re-do rate calculation

    Yolanda Wei

      Hello,

       

      Suppose my raw data looks like the table below - It shows whether a student passed or failed a test with test date information. The students may re-try and take the test again or multiple times if failed in the first try. (The real data set is slightly big (millions rows) but similar to this example table).

       

      I am trying to create a bar chart (or a table) to show the following metrics:

      1. Failure rate (in the first try) - How many students failed the test in the first try per month? (Jan: 3/4 = 75%    Feb: 0/1 = 0%    March: 1/2 = 50%)
      2. Of all the students who failed in the first try, how many of them tried to take the test again? (Jan: 3/3 = 100%  Feb: N/A   March: 0/1 = 0%)
      3. Of all the students who retried the test, how many of them are passed(as the final result)? (Jan: 1/3 = 33%  Feb: N/A  March: N/A)

       

      Note: In the calculation, the date is based on first test date for each student.

       

      ID

      Test_Date

      Student Name

      Result

      1

      1/2/2016

      Bob S.

      FAILED

      2

      1/2/2016

      Samantha

      FAILED

      3

      1/2/2016

      J.J

      PASS

      4

      1/13/2016

      Samantha

      FAILED

      5

      1/15/2016

      Ruby

      FAILED

      6

      1/20/2016

      Bob S.

      FAILED

      7

      2/12/2016

      Ruby

      PASS

      8

      2/12/2016

      Emma

      PASS

      9

      3/1/2016

      L

      FAILED

      10

      3/15/2016

      Amy

      PASS

       

      Any suggestions? Is there any way I can create the charts/tables without creating an extra data table?

       

      Thank you,

      Yolanda

        • Re: Qlikivew Bar chart/ Table: Re-do rate calculation
          John Witherspoon

          It appears to me that you calculated #3 incorrectly for January. Only two students retried the test in January, and neither of them passed, so 0% sounds correct based on your wording. Based on the number, you may have meant "students who passed the test on any attempt". Would a student who passed ever retake the test? If so, what happens if they retake and fail? Or would the pattern always be either all fails, or all fails ending with a single pass?

           

          Anyway, attached are a couple ideas, one with new fields but no new tables, one that doesn't require new fields. Probably neither is exactly what you want, but they produce the right results on your very simple data.