How to create a dimension of date type with custom cohorts?

[nelsonchen]

Hi there, I’m still getting used to Looker but came across an issue where I have gotten stuck. I’m wondering if it is possible to get some ideas on how to approach this problem that I’m trying to solve.

What I want is to aggregate a measure based on a ‘cohorts’ which are assigned on dates. Particularly, if the record in the data is of two years ago, then group by year. If the record is of last year, then group by quarter. If the record of the data is any more recent, then report by month. This way, I can create a view that condenses past data but also presents it. If I keep everything in less granular time frames, unfinished time periods (for example, reporting a year when the year is incomplete) is inaccurate for my purpose. If I report more granular time periods (like months), then I will have several years of data to visualize in a dashboard which can get quite crowded.

My issues are that of the fact is that while I can find the most recent date (using max inside a measure), I cannot reference it in a dimension (I can use SYSDATE in a dimension that references the current time, but would prefer not to as my dataset while updated on a cadence can be lagged at times). So ideally what I want to try is to report more frequent time periods with more recent data, and group more historical data if possible.

So how might I be able to go about grouping dates into this format, in Looker? Any help would be appreciated

Row item:
2017
2018 Q1
2018 Q2
2018 Q3
2018 Q4
2019 Jan
2019 Feb
etc.