How do I create QTD and YTD vs QTD and YTD (last year) looker custom dimensions from date field?

[LucasO1]

Hello all. I'm looking to create some custom dimensions from a date field in Looker. I would like to create the the following - 

• Quarter to date
• Quarter to day (same quarter last year)
• YTD
• YTD (same time last year)

How do I go about this?

Thanks. 

[fabio1]

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_of_year]
}

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} ;;
}

[LucasO1]

@fabio1 So this would need to be done in lookml right?

[fabio1]

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.