週の始まりをフィルターで選択された日付を見て動的に変更する

Looker Q&A 日本人コミュニティ (Japanese)
1

※ 本投稿はLooker Advent Caledar 2021 6日目の記事です。

Lookerでは、LookMLの中でLiquidを使うことができ、より動的で柔軟なロジックを組むことができます。今回は、ユーザが画面で設定した日付のフィルターの開始日を見て、もし水曜日がフィルターの開始日であれば、水曜日始まりで、週の計算をしていくサンプルコードになります。

このロジックがない場合は、基本的には月曜始まりで週の計算をしていくので、水曜日を開始日とした場合は、1週目は他の週よりも値が少なく算出されます。

月曜始まりになるので、2021/8/30から週を始めていますが、中身の計算は、2021/9/1からになります。

水曜始まりになるので、2021/9/1から週を始めています。もちろんそれ以降の週も水曜始まりとなります。

LookML

#元々の日付
  dimension_group: created_at {
    type: time
    timeframes: [date,raw,day_of_week,week]
    sql: ${TABLE}.created_at ;;
    convert_tz: no
  }

#メインのロジック
  dimension: dynamic_week {
    description: "フィルタで選択した期間の開始日を見て、開始日の曜日を週の始まりに設定して週ごとにする"
    sql:
      CASE
      WHEN EXTRACT(DAYOFWEEK FROM {% date_start created_at_date %}) = 1
        THEN FORMAT_DATE('%F', DATE_TRUNC(${created_date} , WEEK(SUNDAY)))
      WHEN EXTRACT(DAYOFWEEK FROM {% date_start created_at_date %}) = 2
        THEN FORMAT_DATE('%F', DATE_TRUNC(${created_date} , WEEK(MONDAY)))
      WHEN EXTRACT(DAYOFWEEK FROM {% date_start created_at_date %}) = 3
        THEN FORMAT_DATE('%F', DATE_TRUNC(${created_date} , WEEK(TUESDAY)))
      WHEN EXTRACT(DAYOFWEEK FROM {% date_start created_at_date %}) = 4
        THEN FORMAT_DATE('%F', DATE_TRUNC(${created_date} , WEEK(WEDNESDAY)))
      WHEN EXTRACT(DAYOFWEEK FROM {% date_start created_at_date %}) = 5
        THEN FORMAT_DATE('%F', DATE_TRUNC(${created_date} , WEEK(THURSDAY)))
      WHEN EXTRACT(DAYOFWEEK FROM {% date_start created_at_date %}) = 6
        THEN FORMAT_DATE('%F', DATE_TRUNC(${created_date} , WEEK(FRIDAY)))
      WHEN EXTRACT(DAYOFWEEK FROM {% date_start created_at_date %}) = 7
        THEN FORMAT_DATE('%F', DATE_TRUNC(${created_date} , WEEK(SATURDAY)))
    END;;
  }

# Helper

  dimension: created_date {
    type: date
    datatype: timestamp
    sql:${created_at_raw} ;;
    convert_tz: no
    hidden: yes
  }

LookMLの解説です。

元の日付のdimension_groupのdateを今回は、画面のフィルターで設定しています。

メインのロジックでは**{% date_start フィルターのフィールド %}**という表現を使っていますが、これがLiquid変数となります。何をやっているかというと、指定したフィルターフィールドの日付の開始日を取ってくる、ということをやってます。

CASE WHENのところで、開始日の曜日を見て、それに合わせて、週の開始日を設定しています。

<注意とか>

今回、元の時間のフィールドであるcreated_atがタイムスタンプ型だったので、扱いやすくするために日付に変換するということをやっています。

また、このロジックはBigQueryに対してのものなので、他のダイアレクトで使用する場合は、また書き直してあげる必要があります。

こんな感じで、Liquidを使うとよりユーザ入力に基づいたロジックを作っていくことができます。

こちらの各リンクもぜひ参考にしてみてください！Bye!

Liquid変数一覧

Liquidでロジック書きたいときに見るリンク

Liquidを使ったLookMLのサンプル集(下の方にある、Liquid use caseのところです)

2 Likes