Custom Date Dimension

I’m trying to take a date field 2021-03-01 (as an example) and turn it into a custom dimension that would be 2021-03 (as an example).

I can use and extract function and concat to pull the year and month and connect them but the output is a string, not a number, 2021-3. If I’m pulling a series, it won’t sort correctly.

Any suggestion on how to change the YYYY-mm-dd format to a YYYY-mm format as a custom dimension?

Solved Solved
0 6 10.3K
1 ACCEPTED SOLUTION

Naomi_Johnson
Participant III

Hi Cory,

As Dawid mentioned, it is best to do this in LookML as custom dimensions are not version controlled and the custom dimension you create will run slower as the calculation is more complicated. 

If it’s not possible to get the month timeframe added to the date dimension group in LookML, there is a workaround:

concat(
extract_years(${view.date})
,"-"
,
if(extract_months(${view.date})<10
, concat("0",extract_months(${view.date}))
,concat("",extract_months(${view.date}))
)
)

It adds a ‘0’ to the month where the month number is less than 10 to give YYYY-mm format.

Hope this helps,

Naomi

View solution in original post

6 REPLIES 6

Dawid
Participant V

In your LookML model use dimension_group of a type:time instead of just dimension.

Add parameter timeframes: [month] and this will display your month as YYYY-mm

In your LookML model use dimension_group of a type:time instead of just dimension.

Add parameter timeframes: [month] and this will display your month as YYYY-mm

I appreciate the reply. I should have specified, that I am new to this whole thing and  I don’t have access to a LookML model so I’m trying to workaround that by creating a custom dimension on a Look.  Here’s what I have to work with:

1a615a60-c4a0-43e1-84ae-92211e0074b0.png

Naomi_Johnson
Participant III

Hi Cory,

As Dawid mentioned, it is best to do this in LookML as custom dimensions are not version controlled and the custom dimension you create will run slower as the calculation is more complicated. 

If it’s not possible to get the month timeframe added to the date dimension group in LookML, there is a workaround:

concat(
extract_years(${view.date})
,"-"
,
if(extract_months(${view.date})<10
, concat("0",extract_months(${view.date}))
,concat("",extract_months(${view.date}))
)
)

It adds a ‘0’ to the month where the month number is less than 10 to give YYYY-mm format.

Hope this helps,

Naomi

Hi, do you know how can I get the yy instead of yyyy in same formula used above. For example I need the date to be dd/yy

Thank you @Naomi_Johnson!  I’m not a fan of workarounds but don’t have a lot of options in this situation.

Naomi_Johnson
Participant III

Glad I could help 🙂

Top Labels in this Space
Top Solution Authors