Filter Dashboard on last working day

[LucieR]

Dear Looker folks,

I need to filter by default my Dashboard on “last working day”, ie :

• if today is tueday, the dashboard needs to show yesterday
• if today is monday, the dashboard needs to show last sunday (date_sub(current_date(), interval 2 day)
• but the user should also be able to modify and choose the day he wants

Any idea ?

Thanks !

[leobardor]

Hi Lucier,

One way to achieve the solution is by creating a new Looker - Dimension group based on your date field.

In my project example, it is a survey database. I have the dimension“date_survey” which is the real date .

To adjust the date based on workdays (Monday to Saturday) I created a second dimension group called “date_survey_last_working_day”

(Full code of the view)

view: surveys_scores {

  sql_table_name: tomy.surveys_scores ;;

  dimension: caring {
    type: number
    sql: ${TABLE}."caring" ;;
  }

  dimension_group: date_survey {
    type: time
    timeframes: [
      raw,
      date,
      day_of_month,
      day_of_week,
      week,
      month,
      quarter,
      day_of_week_index,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}."date_survey";;
  }

  dimension_group: date_survey_last_working_day {
    type: time
    timeframes: [
      raw,
      date,
      week,
      day_of_week,
      month,
      quarter,
      day_of_week_index,
      year
    ]
    convert_tz: no
    datatype: date
    sql: CASE
    WHEN ${date_survey_day_of_week_index} = 0 THEN (date(${TABLE}."date_survey") - INTERVAL '2 days')
    ELSE  (date(${TABLE}."date_survey") - INTERVAL '1 days')
    END
    ;;
  }

  dimension: knowledge {
    type: number
    sql: ${TABLE}."knowledge" ;;
  }

  measure: count {
    type: count
    drill_fields: []
  }


}

1.  Add  day_of_week_index timeframe in your original date field. It will be used to evaluate it against any rule based on day of week.
2. When creating the second dimension, the business logic of generating last_day  date is placed in  SQL Parameter (In my example, a postgreSQL database is used, please modify your SQL code based on your instance)
3. In this solution, a SQL CASE - WHEN clause was implemented in the second dimension

sql: CASE
    WHEN ${date_survey_day_of_week_index} = 0 THEN (date(${TABLE}."date_survey") - INTERVAL '2 days')
    ELSE  (date(${TABLE}."date_survey") - INTERVAL '1 days')
    END
    ;;

After saving changes in your LookML , the explore should have the second dimension available.

You can create a table to validate the new dimension and filtering:

If you choose a date that its day is Monday, the date is adjusted in the column:

Best Regards,

Leo