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

  • 6 December 2021
  • 0 replies
  • 200 views

Userlevel 1

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

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

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

通常のdimension group weekを使った場合

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

 

Dynamic weekを使って水曜始まりで計算した場合

水曜始まりになるので、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のところです)

 

 


This topic has been closed for comments