At Werkspot, we have 1 explore that has a plain table filled with (day)dates from 2013 until 2030 as a base.
The rationale is that we quite often create reports with Forecast numbers and therefore we want to be able to create reports with dates in the future.
Moreover: I would love to define a primary key on this date-view, because I want Looker to prevent to do an unnecessary “Group By” (when I create a report on day-level).
However, I cannot do this on a dimension_group level; it should be on a dimension level.
This is what I came up with:
- dimension: date_date
- dimension_group: date
timeframes: [time, week, week_of_year, day_of_week, month, quarter, year]
However, for the end user this is not ideal, because we now have 1 dimension and 1 dimension-group.
How can I define my primary_key in a way that for the end-user the primary key (in this case: date_date) is simply shown in the dropdown list, together with all the other date-fields?
Sorry, right now there is no way to do this, but it is a great suggestion. We’ll get it tracked.
Thanks for your fast reply Lloyd!
We did a similar thing, but set
on the primary key field so as not to show it to the users.
Thanks for your suggestion Robert.
However, hiding the primary key would not make sense, since the date_date is used quite often. So often, that I want to speed up the reports that are created by our end-users that are using the date_date in their Looks (by preventing Looker to make unnecessary Group By’s) 😉
In other words: the field should be accessible for our end-users.
You’ve probably thought of this already, but you can create separate date, week, month, quarter, and year dimensions — whichever you need — defining them using appropriate SQL date functions, and omit the dimension group altogether.
No, I did not (stupid, huh? 😉 )
Elegant and simple solution!
Ik hoop dat ik niet uw werk spotte.