Hello all. I'm looking to create some custom dimensions from a date field in Looker. I would like to create the the following -
How do I go about this?
Thanks.
My preferred way to do that is for the modeler to make a set of "Is YtD?", "Is WtD?", etc yes/no dimensions, which explore users can then apply as a filter in any generic measure.
Here is one example: https://github.com/looker-open-source/bigquery_information_schema_block/blob/master/views/date.view....
Part of the "trick" is to create a hidden dimension that actually does not apply to any of the table's columns, but instead to the database's "current date" function, like so:
dimension_group: current {
type: time
datatype: timestamp
sql: CURRENT_TIMESTAMP() ;;
hidden: yes # This is mainly for use in other dimension definitions
timeframes: [day_of_week_index, hour_of_day, day_
}
dimension: is_ytd {
group_label: "Is period-to-date?"
label: "Is YtD?"
description: "Is year-to-date? Whether the date in question is earlier within its year than the current date. Useful for filtering to comparable parts of the current period and a past period"
type: yesno
sql: ${my_column_day_of_year} < ${current_day_of_year} ;;
}
@fabio1 So this would need to be done in lookml right?
The solution I suggested above must be done in LookML.
In the explore, using Looker filter expressions, I think you could do YTD like so:
extract_days(${query.created_date})
< extract_days(now())
AND
extract_years(${query.created_date})
< extract_years(now())
QTD is probably possible as well, but there are not native quarter functions, so you would have to do some custom math based on the date.