Question

Custom Fields - Creating a rank column based on earliest date when at specific point

  • 13 August 2021
  • 1 reply
  • 37 views

  • New Member
  • 0 replies

Good day,

I work in shipping and logistics. I’m looking to create a column showing a rank (1,2,3...) based on the sailing date of a ship at a specific port.

FYI All ships start with the same port and there’s a date column for each port, so the easiest way might be to add a filter (?)

Thank you in advance for your insight!


1 reply

I had the same issue. The way I got around this was I created a LookML dimension to get the min date from the table

dimension: min_calendar_date {
    description: "Earliest date"
    type: date
    sql: (SELECT MIN(CALENDAR_DATE) FROM TABLE_NAME)  ;;
    convert_tz: no
  }

 

Then created a custom dimension which check calendar_date against min_calendar_date. 1 if the dates match, 0 if the dates do not match

 

if(${calendar_date} = ${min_calendar_date},1,0)

 

Then used the custom dimension as a filter and limited to 1

Reply