I am not sure I understand where exactly you would like to go with that. Why not try to use a distinct count: COUNTD( [user_id]), and then you can use for example the start date and make it discrete to show it day by day.
Hope this helps a little bit!
Do you want these groups to be grouped by month or overall?
I would like to group this overall. In case my original question was confusing (reading it back i think it might have been)
I am looking to find out the percentage of users who have had activity on one day / v the percentage of users who have come back and had 2 days of activity / v percentage of users who have had 3 days of activity.
Let me know if this makes sense
This kind of cohort analysis is possible in Tableau, there are two general methods: One is to pre-identify your cohorts in your database, whether that's subqueries in the database or subqueries in a Custom SQL data connection in Tableau. The other is to use a series of table calculations. The latter can be entirely done in Tableau, however the table calculations start getting really slow when your volumes get above somewhere between 10-100K records in the view (i.e. after filters have been applied). By slow, I mean 30 seconds and up every time the view is refreshed, as opposed to sub-second to few-second refreshes. This is because the results of table calculations are not materialized in Tableau, so Tableau has to do a bunch of re-calculating every time to create the cohorts, and then perform the analysis on those cohorts.
If your volumes are smaller, I can definitely help out with the table calculations necessary. If they are larger and you need to do this in the database, you'll likely need some other resource, I'm not as much of a SQL wiz as I'd like to be.
In any case, there are a few questions to be resolved:
- Is the day based on the event start time?
- When a user has multiple events of the same type on the same day, does that count as just one "activity" or multiple?
- Is activity counted for each user+event sequence? For example, for plan a and event a, user_id 3 has records with event start time 1/7, 1/25, 2/21, 2/25, 4/2-4/4. Does that count as 5 activities (4x1day and 1x3day), or something else?