Running Total Distinct over time

Reply
Highlighted
Yellow Belt

Running Total Distinct over time

Hi!

I have data for trainings/sessions that have been held over the last year, specifically who attended them.

I would like to visualize how many more unique attendees we are having every session, on each internal team. When I do a distinct count of attendees over time (a running total line graph), it gives me the running total sum of distinct attendees for each session, not session to session. The main question I would like to answer is: are the same people attending these sessions? Or are we attracting new people that are attending? If new people are attending, what team are they on?

Is what I am trying to visualize possible in Domo?

 

Here is an example use case:
Mary and Bill from Team A attended Session #1
Alex from Team B attended Session #1

Mary, Bill, and Jane attended Session #2

Alex from Team B attended Session #2

Team A had 2 unique attendees in Session #1 
Team B had 1 unique attendee in Session #1

 

Team A had 1 unique attendee in Session #2, Running Total: 3

Team B had 0 unique attendees in Session #2, Running Total: 1


Accepted Solutions
Highlighted
Yellow Belt

I think I might use something like this in a MySql transform:

 

SELECT *
, (SELECT COUNT(1) FROM `attendees` p
WHERE p.`Email`=s.`Email`
AND p.`Event Date`<s.`Event Date`
) AS 'Previous Attendance Count'
FROM `attendees` s

 

This will give me a count of how many sessions they have attended, with the first one being '0'. Anything greater than 0 can be a non-unique attendee.

 

 

View solution in original post

Tags (1)

All Replies
Highlighted
Black Belt

I don't think any permutation of COUNT DISTINCT in beast modes will get you the answer you're looking for.

I solve this problem with ETL like this.

https://www.youtube.com/watch?v=Xb4QgKYgaqg


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
How to calculate engagement rates (pct of total) without using count distinct using #MagicETL in #Domo (without pre-aggregation) The 'without preaggregation'...
Highlighted
Yellow Belt

@jaeW_at_Onyx 

I'm not sure if I totally understand how I could use the percent of the total. This would assume that I know how many people could possibly attend, which I do not.

 

Basically, I need to know out of all the attendees for each session, how many have already attended any previous session.

Highlighted
Yellow Belt

I think I might use something like this in a MySql transform:

 

SELECT *
, (SELECT COUNT(1) FROM `attendees` p
WHERE p.`Email`=s.`Email`
AND p.`Event Date`<s.`Event Date`
) AS 'Previous Attendance Count'
FROM `attendees` s

 

This will give me a count of how many sessions they have attended, with the first one being '0'. Anything greater than 0 can be a non-unique attendee.

 

 

View solution in original post

Tags (1)
Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.