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

Qlikivew Bar chart/ Table: Re-do rate calculation

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

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.

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

Hi John,

Thank you very much! The examples you provided are very helpful. I took one of your ideas (the one with new fields) and modified it a little bit to meet my specific needs here, then everything worked out.

Thanks,

Yolanda