テーブル計算を使った小計の計算方法

本記事は Subtotals with Table Calculations 🇺🇸 の翻訳記事です。

本記事はLookerの標準のテーブル計算機能を使って小計を算出する方法を紹介しています。Looker 6.10 では、テーブルネクストが追加されており、これを使うと小計がネイティブに算出できるようになります。より詳しい情報についてはこちらのドキュメントを参照してください。

小計(subtotal)はグループ分けされた項目の合計を手早く確認するのに使われ、例えば、全体の合計の売上数値を見つつ、同時にブランド別の売上の小計なども参照する、というようなケースで便利です。本記事ではこの実現方法についての手順をご紹介します。

これは暫定的なやり方になりますので、こちらのコミュニティーに投稿されている優れたクロス結合による小計(Subtotals with a CROSS JOIN)パターンも参照してください。より柔軟な魔法のようなやり方を学ぶことができます。

下記画像のような例を使って考えてみます。いくつかのブランドがあって、その中にカテゴリーとメジャーがあり、売上合計または利益が算出できるものとします。これを今回のデータセットとして考えます。

さて、ここで、例えばブランドマネジャーがブランド毎の売上合計がどのくらいなのか、つまりそれぞれのブランドの小計が欲しいというケースを考えます。

テーブル計算を使うと、それぞれのブランドの最後の行と同じ行に4つ目の列を生成することができます。

これをやってみましょう。非常にシンプルなやり方です。

方法

ステップ1 テーブル計算を作成して、それぞれのブランド毎に行番号を表示させます。

この番号はブランドが変わるたびに 1,2,3... のようにカウントしていきます。

テーブル計算は次のように書きます。

if(match(${products.brand},${products.brand})=offset(match(${products.brand},${products.brand}),-1)
 , 1+row()-match(${products.brand},${products.brand})
 , 1)

ここでは match() を使ってそれぞれのブランドが最初に登場するユニークな値をチェックしてその行番号を取得します。

ステップ2 次に小計の基準となる partition_row_number を作成します。これが必要になるのは、ブランド毎の行数はそれぞれ異なるためです。例えば !It Jeans の行と Calvin Klein の行をみてください。

小計列では、テーブル計算は次のように書きます:

if(
 NOT(${products.brand} = offset(${products.brand},1)),
 sum(offset_list(${order_items.total_sale_price},-(${partition_row_number}-1),${partition_row_number})),null)

ここでのキーポイントは offset_list() 関数を使って現在の行にあるブランドの値と同じ行を全て取得し、さらに partition_row_number を使ってそれをオフセットし、仮にもしデータ中のブランドが変更されても、あるいは新しいディメンションが追加されたとしても、これらが一致していることを確認するところです。sum() は依然として最初のテーブル計算で計算された行数のみに対して適用され、必要に応じて他のメジャーだけでなく、必要であればディメンションに対してこのパターンを繰り返し適用することができます。

  • 国別

  • 年齢別

これはこちらのWeekly Totalsの内容と実によく似ています。

追記

もしブランドフィールドも一緒にグループ化したい場合は、テーブル計算を次のように記述します。

if(${products.brand} = offset(${products.brand},-1),"",${products.brand})
0 0 869