Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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.
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.
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