Count Unique IDs by Month-Year

tmerchant · 2023-03-13T16:03:07+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Accepted answers

All comments

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?

Screen Shot 2023-03-13 at 10.50.48 AM.png

Did I perform the function correctly?

Screen Shot 2023-03-13 at 10.53.51 AM.png

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.