The problem I’m having
I would like to introduce a metric that is calculated on both: beginning of the week, beginning of the month, end of the week, end of the month time grains.
The context of why I’m trying to do this
I’m trying to do this because our internal definition of the metric can vary depending on the team that is using it. Finance teams are only interested in headcount on the finale day of each period (week, month, quarter) while other teams require taking the whole month into consideration.
What I’ve already tried
I’ve built the mart model with the following fields:
date, is_active, user_id, day_is_first_of_week, day_is_first_of_month, day_is_last_of_week, day_is_last_of_month
Some example code or error messages
Here I’m not sure how to define grain properly as I assume it will calculate count of distinct user_id within the week, month but I would actually need it to consider the end dates of each period as well.
Should I be creating separate metrics based on multiple mart models or can my requirements still be achieved through one metric?