Question

Date_diff errors

  • 16 August 2018
  • 2 replies
  • 666 views

Hi, I am having problem with the date_diff solution for dev:


I currently have:


  dimension_group: ts_1 {
type: time
timeframes: [date, raw]
sql: ${TABLE}.ts_1 ;;
}

dimension_group: ts_2 {
type: time
timeframes: [date, raw]
sql: ${TABLE}.ts_2 ;;
}

dimension: date_diff {
type: number
sql: DATEDIFF(day, ${ts_2}, ${ts_1}) ;;
}

and am receiving an error message in the UI.


Any solutions? Thanks!


2 replies

Userlevel 1

Hey @dancingo,


I hope all is well! For the dimension executing the DATEDIFF, it looks like we are currently referencing the two dimension groups above it instead of referencing the explicit fields we are trying to subtract. What I mean by this is, we have something like:


DATEDIFF(day, ${dimension_group_2}, ${dimension_group_1}) 

when we should be subtracting between two date fields or two timestamp fields. So we should have something more like:


DATEDIFF(day, ${date_2}, ${date_1}) 

Dimension groups are essentially groups of dimensions so if we want to reference a field within each dimension group, we can do this by appending the timeframe to the end of the dimension group name, as mentioned here. So if we want to reference date from ts_1, we would reference it like so: ts_1_date. If we want to reference raw from ts_1, we would append _raw like ts_1_raw.


Let’s try referencing the fields within each dimension group in the sql of the date_diff field instead of referencing the dimensions groups themselves. It will look something like:


dimension: date_diff {
type: number
sql: DATEDIFF(day, ${ts_2_date}, ${ts_1_date}) ;;
}

I hope this helps! Let me know if I can clarify anything about this on my end!


Best,


Leticia

Hi,

Can you help me understand why I am getting an error with the below date_diff expression in ML? I want to get the days between the date (in the grouped dimension) and today’s date.


dimension: date_diff {

type: number

sql: DATEDIFF(day, ${date_joined_date}, GETDATE()) ;;

}


Thank you!

Reply