I have a visualization requirement and I know cumulative count is the answer but unable to figure out how.
There is a base table with list of registered participants. example table below:
RegID | Name | City | Registration Date |
---|---|---|---|
1 | Name1 | City1 | 24-Jan-21 |
2 | Name2 | City1 | 24-Jan-21 |
3 | Name3 | City2 | 24-Jan-21 |
4 | Name4 | City1 | 25-Jan-21 |
5 | Name5 | City2 | 25-Jan-21 |
6 | Name6 | City3 | 26-Jan-21 |
7 | Name7 | City4 | 26-Jan-21 |
8 | Name8 | City2 | 29-Jan-21 |
9 | Name9 | City3 | 29-Jan-21 |
10 | Name10 | City1 | 29-Jan-21 |
I have event table as below:
EventID | Event Name | Date |
---|---|---|
1 | Event1 | 26-Jan-21 |
2 | Event2 | 27-Jan-21 |
3 | Event3 | 28-Jan-21 |
4 | Event4 | 29-Jan-21 |
5 | Event5 | 30-Jan-21 |
One participant can attend multiple events. Event_Log will have who all attended the events as below:
EventID | RegID |
---|---|
Event1 | 1 |
Event1 | 2 |
Event1 | 3 |
Event2 | 5 |
Event2 | 6 |
Event2 | 7 |
I want to show a daily cumulative line chart report of %unique participants coverage
Date | # Registered | # Unqiue Covered | % Covered |
---|---|---|---|
26-Jan-21 | 7 | 3 | 43% |
27-Jan-21 | 7 | 5(note: 1 attended twice so ignored) | 71% |
I also want to City filter on the top to show the same report for selected city!
Sorry for such long description but I feel I am there and yet I am unable to get what I want.