(計算式あり)Tableau Desktop　最新日付を元にしたExcelチックな処理の例

まれに「Excelっぽくデータを表示してほしい」というご要望をいただくことがあります。
TableauとExcelとではデータに対する発想が異なるため、Excelでは簡単にできることがTableauでは難しい場合があります。
仮に静的な条件であればTableau Prep上で事前に処理してしまった方が早いこともありますが、動的な条件の場合、Desktop上で工夫しないといけません。
今回は実際に過去お客様からご要望いただいた事例を参考に、最新日付を基点にした処理を2つご紹介します。(最新日付というのが動的な条件です)

①カテゴリ別×各月毎にその月の利益額と最新月～過去6カ月の間で利益が最も小さい月の利益額を表示したい(表示する期間は直近6カ月とは限らない)
②カテゴリ別×各月毎にその月の利益額と最新月～過去6カ月の平均利益額を表示したい(表示する年月は直近6カ月とは限らない )

文章にすると一体何を言っているんだという内容ですが、イメージはこちらです。

このワークシートでは、カテゴリ別、オーダー日の年月別に複数のメジャーを表示しています。
「家具」カテゴリを例にとると、
「利益」メジャーには毎月の利益額の合計を表示し、
「直近6カ月のうち利益が最小の月の利益額」には、(直近が12月なので)7～12月の6か月の中で一番利益額が小さい8月の利益額を表示し、
「直近6カ月の平均利益額」には、7~12月の6か月間の平均利益額を表示しています。

全く同じ処理を必要とするケースはまれだと思いますが、これらの実装方法がわかるとより一層LOD計算の理解が深まるはずです。
この記事がTableauを使う上でお役に立てば幸いです。

なお、本記事ではそのまま真似すればすぐ出来ることを第一として記載します。
登場する関数の詳細等については公式ドキュメントをご参照ください。
また、以降の詳細な手順はTableauインストール時に同梱されているサンプルスーパーストアのデータを使用して説明します。

①直近6か月のうち利益が最小の月の利益額

1{FIXED [カテゴリ]:
2   MIN(
3        {FIXED  [カテゴリ],DATETRUNC(‘month’,[オーダー日]):
4            SUM(
5                IF DATETRUNC(‘month’,DATEADD(‘month’,-5,{MAX([オーダー日])}))<=[オーダー日] 
6                    THEN [利益]
7                END
8            )
9        }
10   )
11}

解説）

1.{MAX([オーダー日])}　で全データ中の最新日付を得ることができます。
DATEADD(‘month’,-5,{MAX([オーダー日])})　は得た最新日付の値を、DATEADD関数を使ってその日の5カ月前の日付に変換しています。
それをさらにDATETRUNC関数でその5か月前の日付の月初(ついたち)に変換します。
仮に「オーダー日」フィールドの中の最も新しい日付が2024/12/16だった場合、
DATEADD(‘month’,-5,{MAX([オーダー日])})　の結果は2024/7/16、
DATETRUNC(‘month’,DATEADD(‘month’,-5,{MAX([オーダー日])}))　の結果は2024/7/1です。
つまり、上から5行目「IF DATETRUNC(‘month’,DATEADD(‘month’,-5,{MAX([オーダー日])}))<=[オーダー日] 」が意味するのは
「「オーダー日」フィールドの値が2024/7/1以降だった場合」です。
これは「直近6カ月」を実現するための条件式です。
上記の条件に当てはまる場合は「利益」フィールドの値を取得し(6行目)、
当てはまらなければNULLを取得します。（ELSE文を省略すると、条件に当てはまらなかった場合得るものはNULLになるという暗黙的な仕様の為です。）
※直近6カ月を月単位ではなく厳密な6か月間(2024/7/17～2024/12/16)としたい場合、DATEADD(‘day’,1DATEADD(‘month’,-5,{MAX([オーダー日])}))に変えます。
※直近6か月を前月末日～の6か月間(2024/7/1～2024/11/30)としたい場合、5行目をIF DATETRUNC(‘month’,DATEADD(‘month’,-5,{MAX([オーダー日])}))<=[オーダー日]  AND [オーダー日] <=DATEADD(‘day’,-1,DATETRUNC(‘month’,{MAX([オーダー日])}))　に変更します。

2.1.を {FIXED  [カテゴリ],DATETRUNC(‘month’,[オーダー日]):SUM　でくくっています。
DATETRUNC(‘month’,[オーダー日])　では、「オーダー日」フィールドの日付をその日付の月の月初に変換しています。
そのため
        {FIXED  [カテゴリ],DATETRUNC(‘month’,[オーダー日]):
            SUM(
                IF DATETRUNC(‘month’,DATEADD(‘month’,-5,{MAX([オーダー日])}))<=[オーダー日] 
                    THEN [利益]
                END
            )
        }
は、「カテゴリ」×「オーダー日の年月」別に合計収益を算出します。
（ただし、直近6カ月ではない年月の合計収益はNULL）

3.計算式ではそれを更に{FIXED [カテゴリ]:MINでくくっているので、
結果として＜「カテゴリ」×「オーダー日の年月」別に算出した合計収益＞のうち、「カテゴリ」ごとに一番小さい収益額　だけを取得します。

②直近6か月の平均利益額

1{FIXED [カテゴリ]:
2    SUM(
3        IF DATETRUNC(‘month’,DATEADD(‘month’,-5,{MAX([オーダー日])}))<=[オーダー日] 
4            THEN [利益]
5        END
6    )/6
7}

解説）

1.3行目～5行目は①１.と同じ処理なので詳しい解説は割愛しますが、結果としてこの範囲では
「「オーダー日」フィールドの値が2024/7/1以降だった場合「利益」フィールドの値を取得し、当てはまらなければNULLを取得しています。

2.それを{FIXED [カテゴリ]:SUM(　でくくっているので、
1.で取得した結果をカテゴリ別に合計し、それを6(か月)で割った結果を得ます。

以上、Excelチックな処理をTableauで(無理やり)行う方法でした。