Unique Coverage by Time

#1

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.

#2

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?

#3

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.

#4

@jiwnaiakbar Yeah, until we can have Distinct([column]) inside of other functions, then you would have to do something like that in SQL.

#5

Yes. But I am struggling with SQL :slight_smile: issue is if I do cumulative sum and group by date, it wont handle one participant attending multiple events on different days as unique.

#6

@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.

#7

Yes. I can try that. Also just found one similar scenario on stackoverlow. Thanks @flamber

#8

@jiwnaiakbar Please add a link here too, when you find the solution.

#9

https://stackoverflow.com/questions/40515839/cumulative-distinct-count very similar. I am still trying to implement the full logic

#10

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