LookML - how to generate week number in LookML

[sysph]

Hi there, i’m pretty new to Looker. Looking for some help please.
Are we able to concatenate ‘week’ with CAST(WEEK(${TABLE}.“CREATED_DATE”) AS STRING)) ;; to create week number. I would like to have 2 digits (eg. week00 week01 …) because when i’m ordering the week number. The order will become week1, week10, week11, ...week2, ...

I tried to use the formate but i’m not sure how to set it like week#0/ week##/ week00 and none of them work when i tried for dimension group record_created and created_week_dim.

I did try the following however it keeps showing warning with ENGTH()

dimension: created_week_dim_string {
type: string
sql:

{% if LENGTH(CAST(WEEK(${TABLE}."CREATED_DATE") AS STRING)) ==2 %}

CONCAT('week', CAST(WEEK(${TABLE}."CREATED_DATE") AS STRING)))

{% else %}

CONCAT('week0', CAST(WEEK(${TABLE}."CREATED_DATE") AS STRING)))

{% endif %} ;;
}

thanks

dimension_group: record_created {
type: time
timeframes: [raw, date, week, month, quarter, year]
convert_tz: no
datatype: date
sql: ${TABLE}."CREATED_DATE" ;;
}
dimension: created_week_dim {
type: number
sql: week(${TABLE}."CREATED_DATE") ;;
}
dimension: created_week_dim_string {
type: string
sql: CAST(WEEK(${TABLE}."CREATED_DATE") AS STRING)) ;;
}

[rallaking]

Hi, I see what you’re trying to do.

I’m a big fan of DRY (Don’t Repeat Yourself) code. If I was approaching this, I would edit the dimension_group to include week_of_year as one of the timeframes and then reference that in the sql line for the new dimension with the string field.

By leveraging the exisiting timeframe and using the SQL code over Liquid syntax, you could drop the extra helper dimensions of created_week_dim and created_week_dim_string. SQL Concat will automatically convert the number to string as part of the function.

dimension_group: record_created {
    type: time
    timeframes: [
      raw,
      date,
      week,
      week_of_year,
      month,
      month_name,
      month_num,
      quarter,
      quarter_of_year,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.record_created ;;
  }

dimension: created_week_dim_string {
type: string
sql: if(${record_created_week_of_year} < 10, CONCAT('week0',${record_created_week_of_year}),
CONCAT('week',${record_created_week_of_year}));;
hidden: no
}

Results in Looker

[sysph]

Thanks @rallaking It’s totally working.