Count Unique IDs by Month-Year

tmerchant

Hi all,

I have a date column (formatted as a timestamp ex. 2022-02-17T23:57:03Z) and a column of Unique IDs in my dataset. I am trying to create a new count column which provides the count of each Unique ID within a certain Month-Year in each row of my dataset. For instance, if I have 3 unique counts of Unique IDs in 2022-02, I would like the data output to look something like this:

Unique ID|Date|Count

abc|Feb-22|3

bca|Feb-22|3

cba|Feb-22|3

How would I go about this? I attempted both Pivot and Group By functions in Magic ETL, though neither gave me the desired output.

Thanks in advance!

MichelleH

HI @tmerchant You will need to first create a calculated field using a Formula tile that extracts the month and year of your timestamp column:

concat(YEAR(`Timestamp`),'-',MONTH(`Timestamp`))

Then use a Group By tile to group by the Month/Year and Unique ID, counting the Unique ID field.

tmerchant

Hi @MichelleH thanks for answering! The above gives me the count of each unique ID, which is 1, but I want the count of each unique ID per month-year. For instance, I want it to say 15 in the Count of ID column in the screenshot below. Is there any way to specify that within the Group By function?

Did I perform the function correctly?

MichelleH

Hi @tmerchant in that case you would want to remove Unique ID from your Group By fields. Then you can join back to previous tile on the year/month field.