Hi @Anthony Labib? , thank you for your question.
We've discussed this also in an email thread, but allow me to answer here as well =]
Solution (rolling distinct from the first month of the table, rather than "calendaric year" is at the end)
I've created a sample table with 24 records. Each record represents a random date in each given month, across 2 years.
I've used numbers for the different store names - just for example.
The next step would be to extract the year and quarter portions of the dates. (some functions can be nested inside each other to minimize recipe steps; however, I'm prioritizing readability)
We use the YEAR() function to extract the year:
The quarter is less straightforward. For this, we extract the month by using the MONTH() function (same way). Then, we divide it by 3 - while rounding it up:
I'm using the MERGE() function to ensure the quarter is within the context of the specific year.
Next, we're creating the aggregations. We're interested to know how many distinct stores are in each year+quarter.
For this, we use the New Formula transform, and the COUNTDISTINCT() function:
Now, if we simply want an aggregated presentation of the info - we'll use the Group-By Transform:
And that's our final output
an alternative approach - counting months from the first row in the table
Not sure if you're interested in this, but I thought to add that approach.
So instead of measuring the rolling years+months through "calendaric eyes", we can do it relative to the first date in the table.
For this, we use the DATEDIF() function to calculate the difference in dates between the minimum date and the current record's date:
?
- We're reaching the 6th quarter since the beginning of the data
- We're nesting 3 functions within one another. MINDATE() inside DATEDIF() inside CEILING().
The process is the same from here, using the COUNTDISTINCT() function and Group-By transform.
That's it. I hope that makes sense; please feel free to ask any additional questions regarding the matter.
Thanks,
Amit.
