この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
さがらです。
LookerのSingle ValueではComparisonオプションより別の値を表示することが出来ますが、Comparisonはただ別の集計値を表示するだけで、2つの集計値の差分値を表示することが出来ません。(割合は出せますけども。)
今回この問題に関わる内容として、”今月1日~昨日までの合計”と”今月1日~一昨日までの合計”の差分をComparisonとして表示するということをやってみたので、備忘録的に手順をまとめておきます。
対象とするLookML
まず、実装前のLookMLの内容を記しておきます。
このviewファイルで定義されている、sale_price
について、”今月1日~昨日までの合計”と”今月1日~一昨日までの合計”の差分をComparisonとして表示する方法を次章で説明していきます。
## modelファイル
connection: "aaaaaaaaaa"
named_value_format: yen_0 {
value_format: "\"¥\"#,##0"
}
explore: order_items_test{}
## viewファイル
view: order_items_test {
sql_table_name: "PUBLIC"."ORDER_ITEMS";;
dimension_group: delivered {
type: time
timeframes: [
raw,
date,
month
]
sql: ${TABLE}."DELIVERED_AT" ;;
}
dimension: sale_price {
type: number
sql: ${TABLE}."SALE_PRICE" ;;
}
measure: total_sale_price {
type: sum
sql: ${sale_price} ;;
value_format_name: yen_0
}
}
実装手順
1.LookMLで必要なフィールドを定義する
先程のLookMLのviewファイルに対して、以下の3つのフィールドを追加します。※注意点:接続先はSnowflakeのため、SQLの内容は接続先のDB/DWHに併せて修正してください。
## 上述のviewファイルの末尾に続けて記載
measure: total_sale_price_until_yesterday{
type: sum
value_format_name: yen_0
sql: CASE WHEN EXTRACT(DAY FROM ${delivered_raw}) < EXTRACT(DAY FROM CURRENT_DATE()) THEN ${sale_price} ELSE NULL END ;;
}
measure: total_sale_price_until_2daysago{
type: sum
value_format_name: yen_0
sql: CASE WHEN EXTRACT(DAY FROM ${delivered_raw}) < EXTRACT(DAY FROM CURRENT_DATE())-1 THEN ${sale_price} ELSE NULL END ;;
}
measure: total_sales_price_comparison {
type: number
value_format_name: yen_0
sql: ${total_sale_price_until_yesterday} - ${total_sale_price_until_2daysago} ;;
}
ポイントは上2つのmeasureそれぞれでCASE文を用いて、「1日~昨日まで」「1日~一昨日まで」、それぞれの期間で集計できるように条件分岐させています。
その後、一番下のmeasureではnumber型を用いて2つのmeasureの差を求めるように定義し、差分値がわかるような仕様としています。
2.ExploreでVisualizeする
2-1.必要なフィールドの選択
下記のように、フィールドを3つ選択します。
- LookMLのCASE文で用いた日付型のdimension
- ”1日~昨日までの合計”を求めるmeasure
- ”1日~昨日までの合計”と”1日~一昨日までの合計”の差分を求めるmeasure
2-2.フィルタの設定
今月のデータだけ表示されれば良いため、LookMLのCASE文で用いた日付型のdimensionに対して、is in the past、1、monthsでフィルタを設定します。
ここまで出来たら、一度実行しましょう!
2-3.Comparisonの設定
後は、下図のようにComparisonの設定をして完成です。
この方法のメリット
私は検証出来ていないですが、type:running_totalのmeasureを上手く使うことで同じことは出来る…かもしれません。
ですが、仮にrunning_totalのmeasureを使うことで実現出来たとしても、今回ご紹介した方法ならではのメリットがあると考えています。
- メリット1:LookMLで記述しているため臨機応変に比較対象を変更できる
- 比較対象のmeasureをどちらもLookMLで定義しているので、例えば全く別のフィールドを参照したmeasureと比較するように差分値の定義を変更することが出来ます。仕様変更を考えたときに容易に対応できることが、LookMLで定義した場合の強みです。
- メリット2:仮にテーブル計算を用いてしまうと、円マーク用のvalue_formatの記述が都度必要
- 違う方法で実現しようとしたときにテーブル計算をもし使用していると、円マークの定義がそれぞれのテーブル計算の設定で必要となります。
- テーブル計算1つならばそこまで大変ではないですが、複数フィールドがあると「単位は円だけど、小数点第二位まで表示させたい…」みたいな仕様変更が発生した時、すべてのタイルを確認して修正する必要があるので、正直やっていられなくなると思います。
最後に
LookMLで実現できる場合には、積極的にLookMLで定義していきましょう!