Question

Cumulative Distinct Count

kate_green
Rank
Badge
My goal is to show a cumulative view of the year, broken down by months or quarters, of the total number of accounts that have performed a certain activity (this activity is stored in a custom MDA table and is a Yes/No dropdown field).  



If the accounts that have completed the activity during January total 100, and then the accounts that complete the activity in February total 100, but some of those accounts repeated the activity, I only want to count the Accounts once for the total cumulative view.  So, for example, the total after February wouldn't be 200 but rather maybe 143, if 57 accounts repeated the same activity in February.  



My thought was that a Cumulative view of a report on a Count Distinct of Account Name would show me what I want, but the same accounts are being counted twice:







I confirmed this by exporting the results and pivoting to count the number of Account Names, which should be 1940 for March 2018 in this example.



Has anyone created anything similar to this report?  Any ideas for tracking a cumulative count of accounts over the course of the year?

4 replies

andy_roy
Rank
Userlevel 2
Badge
It sounds like you only want to count the first time this activity is completed for each account...is that correct?  If so, maybe set a date field as well for "first time activity x completed", populating with the current date when the activity is completed, but only if it's currently null.  This way, each account would have one date for that first completion.  Then you could group your report by that field.  Did that make sense?  🙂
kate_green
Rank
Badge
Thanks for your comment Andy - and I like where your head's at.  The Minimum Date idea is a good one.  My hesitation is that I want the drill down data to show all of the occurrences of the activity being completed.  And the complex part is, there are various titles of events that would qualify an account for having completed an activity.



dan_ahrens
Rank
Userlevel 7
Badge +2
I like Andy's solution. You'd want to capture the first date that the event happened and (at least for the purposes of this report) ignore any subsequent events.



If you want to also show all the occurrences of the event(s) per customer, then you'd have to filter your data differently in a different report. 
VP of Customer Success at Forcepoint
andy_roy
Rank
Userlevel 2
Badge
Perhaps setting milestones would also help?  You're right--it's complex enough that I don't fully follow your full use case.  The "first time activity completed" date could help track overall firsts.  If you want to know also when each title/type of event was completed, maybe you set a milestone for each?  

Reply


Terms & ConditionsCookie settings