Count Unique IDs by Month-Year

tmerchant
tmerchant Member
in Beast Mode

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!

Answers

  • MichelleH
    MichelleH Coach

    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
    tmerchant Member
    edited March 2023

    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
    MichelleH Coach

    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.

Categories