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.

[Dawid]

If you use PDT then you could achieve it in SQL using CASE. If your view is built directly from a table then you still could create this dimension in LookML, also using CASE. If you only need it in one look then Custom Dimension would be your friend.

In any case you’re just grouping data by a transformation of a timestamp…

This is just a logic example, you’d have to adapt it to your dialect of course

CASE
    -- For this year and this year - 1, show months
    WHEN YEAR(CURRENT_DATE()) - 1 <= my_timestamp THEN DATE_TRUNC(my_timestamp, MONTH)
    -- For two years ago show quarters
    WHEN YEAR(CURRENT_DATE()) - 2 < my_timestap THEN DATE_TRUNC(my_timestamp, QUARTER)
    -- Anything else is a year
    ELSE YEAR(my_timestamp)
END

[nelsonchen]

@Dawid This is helpful! I am able to adapt your framework to transform my groupings

If it is possible and I want to use dates from the dataset, how do you reference the max date of the dataset in LookML? Reason being that while I do expect this dataset to update at regular intervals, sometimes it is lagged so I would like to avoid relying on CURRENT_DATE() if possible

I am able to pull the max date as a measure in Looker, but not bring it into a dimension as a reference. Is there a workaround with this?

Edit: I think I need to make a dimension that references it as a subquery. With that, I should be okay. Thanks for your help!