Custom Timeframes for Dimension Groups

Looker’s dimension groups for date/time fields make it easy to add multiple different dimensions for different timeframes, including day/week/month, quarter, and more recently some extra ones like hour6/hour12 etc and similar time-chunks for minutes and even milliseconds.

But what if you need something else? Recently I helped a developer set up a ‘fortnight’ timeframe.

In terms of SQL this is an interesting little problem to solve. First you need to decide when your fortnights start - here I’ve chosen the first Monday of the year, 2021-01-04. Then we want to round/truncate our dates to the start of the previous two-week period.

To do this, we’ll use DATEDIFF to get the number of days since our reference Monday, and then use MOD to do some good old modular arithmetic - i.e. get the remainder after dividing the number of days by 14. This will give us numbers 0-13 which we can then subtract (using DATEADD but subtracting the number from 0 to do a date subtraction). We can use this SQL for a dimension with type: date.

Now we have our dimension, but we want it to appear in the field picker with the rest of the the timeframes. Luckily you can just add a group_label which matches the automatic group label for the dimension group - in this case, Created Date. I also used the label and group_item_label parameters to match the behaviour of the existing timeframes, with a short label within the group in the field picker but the full label in the table header and elsewhere.

Note that this won’t work for dates before our reference Monday date. To solve that you could choose an earlier date, or use DATEADD to move the reference date into the past.

Another thing to note is that Looker’s ‘fill in dates’ function will helpfully try to add add in the missing days which is probably not what you want. You can remove the filled in dates in the Explore UI, but then if any fortnights don’t have data your charts might end up looking a bit odd. It’s probably unlikely that an entire fortnight has no data but it’s possible. I don’t have a good solution for this yet but would be keen to hear ideas!