Qlikivew Bar chart/ Table: Re-do rate calculation

[Anonymous]

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

[johnw · Accepted solution]

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.

[Anonymous]

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