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.
Hi @jiwnaiakbar
Isn’t this almost the same as https://github.com/metabase/metabase/issues/14523, but just with a slight difference instead of Sum(Distinct([column]))
, then you want CumulativeSum(Distinct([column]))
Or am I misunderstanding this?
Hi @flamber it is same base date but I want to show a trend on cumulative basis. so this would be something like cumulative count (distinct attended_id) / cumulative count (distinct total ID) GROUP BY DATE.
@jiwnaiakbar Yeah, until we can have Distinct([column])
inside of other functions, then you would have to do something like that in SQL.
Yes. But I am struggling with SQL issue is if I do cumulative sum and group by date, it wont handle one participant attending multiple events on different days as unique.
@jiwnaiakbar Okay, couldn’t you do a count(distinct concat(attended_id, event_id))
?
Again I don’t know your data or which database you’re using, but it sounds like stackoverflow.com or similar forums would be much better at helping you with the SQL. I’m sure that someone has asked something similar before.
Yes. I can try that. Also just found one similar scenario on stackoverlow. Thanks @flamber
@jiwnaiakbar Please add a link here too, when you find the solution.
https://github.com/metabase/metabase/issues/3807 this one solves it. I mean feature request is open but @mfpinhal has given the query.
1 Like