Count New Ids by time period

nmizzell
nmizzell Contributor
in Beast Mode

Hi Domo,

I would like to calculate new IDs by time period. For example, if there are 100 new ids in the month of September that have not appeared before in any other month, I would like the function to return 100. A simple count distinct will not do, since it counts all distinct values by time period, including returning IDs that are present in previous months. I would like the proposed function to return only the count of new IDs.

Thanks

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Does your data have a date field that indicates when the ID was added?

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach

    You would have to do some work in ETL. I would create a recursive dataflow with a monthly snapshot. Then you could do a join of the current month in one branch and all other historical data in the other branch. Any ID's in the current month branch that don't find a matching ID in the historical data would be new ID's and then you would do a count distinct as you suggested.

    If I solved your problem, please select "yes" above

  • nmizzell
    nmizzell Contributor

    Thanks, Colemen,

    Could you provide an example of what the dataflow would look like? I would like to have the calculation run on all months and keep the data in one dataset. It sounds like a recursive dataflow will only give me one month at a time.

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Does your data have a date field that indicates when the ID was added?

    If I solved your problem, please select "yes" above

  • nmizzell
    nmizzell Contributor

    Yes, it does, this makes it so much easier. I can just count ids by date added. Thanks Colemen!

  • ColemenWilson
    ColemenWilson Coach

    Yes, that makes it much easier! Just date added on the x-axis and then a COUNT(DISTINCT(`id`)) beastmode on the y-axis.

    If I solved your problem, please select "yes" above

Categories