Subtract dimension from date dimension_group


(Sarah Ferguson) #1


I have a date dimension group and a dimension called ‘extra days’ which is numeric (and derived from a calculation between two other dimensions). I want to subtract ‘extra days’ from the date but keep getting errors when using date_add.

Can anybody suggest how to structure this in LookML so I don’t get errors such as ‘Failed to retrieve data - Invalid cast from INT64 to TIME at [34:134].’


(Izzy) #2

It’d be helpful to see the LookML for the dimension group and dimension to see what might be going wrong!

(Sarah Ferguson) #3

These are the two dimensions I’m trying to combine:

dimension_group: promised_delivery {
type: time
timeframes: [
sql: ${TABLE}.promised_delivery_date;;

dimension: extra_days {
type: number
sql: ${package_dispatch.extra_days} ;;

And this is what I’ve tried to do (I’ve done it multiple ways but none seem to work):

dimension: promised_date_no_extra_days {
type: date
sql: date_add(${promised_delivery}, -{extra_days}, day);;

(Izzy) #4

I gave it a whirl and ran into a similar error (I’m on bigquery, so it was semantically a little bit different) but I found 2 ways to fix it. Not sure if it’s the exact same problem as you, but it looks like it’s caused by the underlying field being in epoch instead of timestamp or datetime.

For me, while this failed:

dimension: promised_date_no_extra_days {
  type: date
  sql: date_sub(${date_raw}, INTERVAL ${extra_days} DAY);;

these two options worked gr8:

best option:

  1. using the _date timeframe from the dimension group, rather than the _raw or a direct table reference. This should automatically apply the proper casting.
dimension: promised_date_no_extra_days {
  type: date
  sql: date_sub(${date_date}, INTERVAL ${extra_days} DAY);;
  1. A complicated series of casts to get the ${date} field into the ‘date’ type instead of int64.
dimension: promised_date_no_extra_days {
  type: date
  sql: date_sub(CAST(TIMESTAMP_SECONDS(${TABLE}.date) AS DATE), INTERVAL ${extra_days} DAY);;

Might be a bit different for you depending on dialect, but if you can’t get it off those pointers, feel free to post the generated SQL from the erroring query and I’m happy to check it out.