Creating a Rolling Unique ID Count by Month (YTD)

[tsilverman_]

Hello,

     Hoping to gather some insight on an issue that I am currently stuck on. I am trying to gather a Unique count by month on a rolling basis. For example, Customer A shows up in the month on Jan, Feb, and March. Customer B shows up in March, and Customer C shows up in March and April. 

I would need to generate the rolling unique count for IDs that showed which would look like this:

Jan - 1

Feb - 1

Mar - 3

Apr - 4

..(all the way to the end of the year)

Each unique ID would be retained going forward, and any new ID would be added for that month. 

I have attached a sample data sheet. 

Any help would be much appreciated

[JamesCharnley]

Hi @tsilverman_ 

To get a unique count of IDs for a month you can grab the month from the date field with DateTimeFormat to aggregate by month, and then take the count distinct of IDs. This doesn't retain the IDs going forward but I'm sure that step could be added easily with a bit more guidance about exactly what format you're looking for. This would get you the table in your post.

When you say go up to the end of the year, do you mean to the max date or including future months with a 0?

[Sarreddy]

@tsilverman_  My solution.

[Christina_H]

Here's my solution, generating the additional rows required to include people in subsequent months