Tableau 日付計算に関するチートシート

日付計算に関するチートシート

はじめに

本投稿は、Ken Flerlageによる素晴らしい投稿 日付計算に関するチートシート を基にしています。
Tableauのワークブックもわかりやすい日本語へしたものを作成しました。

日本語版ワークブックはこちらからダウンロードできます。必要な計算をコピーするだけで46の再利用可能な日付計算を利用できます。

Tableauの日付と時刻に関連する基本的な関数について精通していることを前提としており
それらについてはここで詳しく説明しません。
もしわからない関数は日付関数の公式のヘルプよりご確認ください。
tableauヘルプ:日付関数

概要

46個の日付に関する計算フィールドを紹介します。
そのうち42個の計算フィールドは、週、月、四半期、年の期間と、
前・現在・次の時間軸で以下のような結果を返すようになっています。

• 各期間の最初の日と最後の日
• 各期間の日数
• 各期間の平日の数

残りの4つの計算フィールドは以下のようになります。

• Decade(10年)
• Century(100年)
• 月の最後の指定した曜日の日 - 例えば、その月の最後の日曜日。
• 月のN回目の指定した曜日の日 - 例えば、その月の第3水曜日。

最初の42個はすべて共通したテーマなので、ここでは月の計算の説明のみします。
週、四半期、年の計算についてはワークブックを参照してください。

注：これらの計算に[日付]というフィールドを利用していますが、このフィールドには、
データ内の任意の日付やパラメータを指定することができます。

最初の日か最後の日

DATETRUNCは日付/時刻型を返すので、これを日付型に変換しています。

"現在の月の最初の日"
// 1) 月の最初の日に切り捨てる
// 2) 日付型に変換
DATE(
    DATETRUNC('month', [日付])
)

"現在の月の最初の日"
// 1) 月の最初の日に切り捨てる
// 2) 1ヶ月分を足す
// 3) 1日分を引く
// 4) 日付型に変換
DATE(
    DATEADD('day', -1, 
        DATEADD('month', 1, 
            DATETRUNC('month', [日付])
        )
    )
)

"次の月の最初の日"
// 1) 月の最初の日に切り捨てる
// 2) 1ヶ月分を足す
// 3) 日付型に変換
DATE(
    DATEADD('month', 1, 
        DATETRUNC('month', [日付])
    )
)

"次の月の最後の日"
// 1) 月の最初の日に切り捨てる
// 2) 2ヶ月分を足す
// 3) 1日分を引く
// 4) 日付型に変換
DATE(
    DATEADD('day', -1, 
        DATEADD('month', 2, 
            DATETRUNC('month', [日付])
        )
    )
)

"前の月の最初の日"
// 1) 月の最初の日に切り捨てる
// 2) 1ヶ月分を引く
// 3) 日付型に変換
DATE(
    DATEADD('month', -1, 
        DATETRUNC('month', [日付])
    )
)

"前の月の最後の日"
// 1) 月の最初の日に切り捨てる
// 2) 1日分を引く
// 3) 日付型に変換
DATE(
    DATEADD('day', -1, 
        DATETRUNC('month', [日付])
    )
)

日数

うるう年でも動作する計算を構築します。
基本的には上で作成した計算式を使って月の最後の日を見つけ、その日付の日の部分を整数として取得します。
すでに作成した計算を参照するだけでもよいのですが、必要なものだけをコピーできるように、
これらの計算を互いに独立させています。

"現在の月の日数"
// 現在の月の最後の日を取得した後、日付の日の部分を整数として取得します
// DAY()内の計算式は、[現在の月の最後の日]と同じです
DAY(
    DATEADD('day', -1, 
        DATEADD('month', 1, 
            DATETRUNC('month', [日付])
        )
    )
)

"次の月の日数"
// 次の月の最後の日を取得した後、日付の日の部分を整数として取得します
// DAY()内の計算式は、[次の月の最後の日]と同じです
DAY(
    DATEADD('day', -1, 
        DATEADD('month', 2, 
            DATETRUNC('month', [日付])
        )
    )
)

"前の月の日数"
// 前の月の最後の日を取得した後、日付の日の部分を整数として取得します
// DAY()内の計算式は、[前の月の最後の日]と同じです
DAY(
    DATEADD('day', -1, 
        DATETRUNC('month', [日付])
    )
)

四半期と年の計算フィールドは、月の計算フィールドとは少し異なります。
四半期については、四半期の初日から最終日までの日数を計算し、1日を加算します。
年については、その年の最後の日の日付部分を取得します。
1週間の日数は常に7日なので、週の日数計算フィールドはありません。

平日の数

ある期間の平日の数は、週末を捨てる必要があるため、日数よりも計算が少し難しいです。
幸いなことに、Tableauにはこのための素晴らしいナレッジベース記事があります。
日本語: 複数の日付の間にある曜日の数を計算する
英語:Calculating Number of Weekdays Between Dates

ここではこのテクニックを活用しています。
ロジックは独立した計算フィールドに詰め込むには少し多すぎるので、「現在の月の平日の数」には
「現在の月の最初の平日」と「現在の月の最後の平日」の2つのヘルパー計算があり、
これらは最初のセクションで作成した現在の月の最初の日と最後の日の計算を参照しています。
そのため、これらの計算を使用する場合は、これらの計算に依存している計算もコピーする必要があります。

"現在の月の最初の平日"
// 次の平日に日付をずらす
IF DATEPART('weekday', [現在の月の最初の日]) = 1 THEN 
    // 日曜なので、1日分を足して月曜に
    DATEADD('day', 1, [現在の月の最初の日])
ELSEIF DATEPART('weekday', [現在の月の最初の日]) = 7 THEN 
    // 土曜なので、2日分を足して月曜に
    DATEADD('day', 2, [現在の月の最初の日])
ELSE 
    // すでに平日のもの
    [現在の月の最初の日]
END

"現在の月の最後の平日"
// 前の平日に日付をずらす
IF DATEPART('weekday', [現在の月の最後の日]) = 1 THEN 
    // 日曜なので、2日分を引いて金曜に
    DATEADD('day', -2, [現在の月の最後の日])
ELSEIF DATEPART('weekday', [現在の月の最後の日]) = 7 THEN 
    // 土曜なので、1日分を引いて金曜に
    DATEADD('day', -1, [現在の月の最後の日])
ELSE 
    // すでに平日のもの
    [現在の月の最後の日]
END

"現在の月の平日の数"
// 2つの日付の間にある平日の数を計算する計算式です
// 期間の最初と最後の平日を計算する計算フィールドを使用します。
// https://kb.tableau.com/articles/howto/calculating-the-number-of-business-days-in-a-month?lang=ja-jp
DATEDIFF('day', [現在の月の最初の平日], [現在の月の最後の平日]) + 1
- 2 * DATEDIFF('week', [現在の月の最初の平日], [現在の月の最後の平日])

月の指定した曜日の日

・月の最後の指定した曜日の日
ここでは例えば、その月の最後の日曜日を取得したいとします。
これは少し複雑なので、できるだけわかりやすくするために、
「現在の月の最後の平日のインデックス番号」を取得するヘルパー計算を1つ作ります。
また、このために以前に作成した計算を使用しています。

"現在の月の最後の平日のインデックス番号 - Helper Calc 1-1"
DATEPART('weekday', [現在の月の最後の日])

曜日ごとに7つの計算フィールドを用意するのではなく、「曜日」というパラメータを作成し、
そこに曜日の数字を入れています（日曜日＝1、月曜日＝2など）。このパラメータを使って、
必要な曜日を指定することができます（または、このパラメータを削除して、計算にハードコーディングすることもできます）。
計算されたフィールドは、月の最後の日から始まり、指定された曜日に達するまでさかのぼるよう計算されます。

"月の最後の指定した曜日の日"
// 現在の月の最後の平日から始めます
// 指定した曜日になるように日数を引いていきます
[現在の月の最後の日]
-
IF [現在の月の最後の平日のインデックス番号 - Helper Calc 1] = [曜日] THEN
    // 最初の日は正しい平日なので、日数を追加する必要はありません
    0
ELSEIF [曜日] > [現在の月の最後の平日のインデックス番号 - Helper Calc 1] THEN
    // 必要な日は最初の日の後です
    7-[曜日]+[現在の月の最後の平日のインデックス番号 - Helper Calc 1]
ELSE
    // 必要な日は最初の日の前です
    [現在の月の最後の平日のインデックス番号 - Helper Calc 1]-[曜日]
END

・月のN回目の指定した曜日の日
ここでは例えば、その月の第3水曜日を取得したいとします。
この計算フィールドのロジックは上述のものと似ていますが、
返したい日が第何週目か（1週目、2週目、3週目、4週目、5週目）を考慮する必要があります。
「曜日」を使用したのと同じように、取得する日が第何週目かを指定するために
「第N週目」という別のパラメータを作成しました。また、ここでも「曜日」を使用します。

この計算フィールドのロジックは少し複雑なので、2つのヘルパー計算を作成しています。

"現在の月の最初の平日のインデックス番号 - Helper Calc 2-1"
DATEPART('weekday', [現在の月の最初の日])

次に、「月の最後の指定した曜日の日」の計算フィールドと同様のロジックで計算フィールドを作成します。
しかし、2つの主な違いがあります。1つ目は、常に月の初日から数えていくことです。
2つ目は、計算の際に第何週目かを考慮していることです。必要に応じて7日間の倍数を加算します。

"N回目の指定した曜日の日 - Helper Calc 2-2"
// 現在の月の最初の平日から始めます
// 指定した曜日になるように日数を足していきます
// その後、7日間の倍数を追加して、指定された週/第N週目になるようにします
[現在の月の最初の日]
+
IF [現在の月の最初の平日のインデックス番号 - Helper Calc 2-1] = [曜日] THEN
    // 最初の日は正しい平日なので、日数を追加する必要はありません
    0
ELSEIF [曜日] > [現在の月の最初の平日のインデックス番号 - Helper Calc 2-1] THEN
    // 必要な日は最初の日の後です
    [曜日]-[現在の月の最初の平日のインデックス番号 - Helper Calc 2-1]
ELSE
    // 必要な日は最初の日の前です
    7-[現在の月の最初の平日のインデックス番号 - Helper Calc 2-1]+[曜日]
END
+
7*([第N週目]-1)

月によっては、ある曜日に5週目が存在することもあれば、4週目までしか存在しないこともあります。
そのため、5週目を選択したのに、該当日が存在しないという状況に対処する必要があります。
この問題に対処するため、最後の計算フィールドでは、その日が現在の月の最初の日と同じ月にあるかどうかをチェックします。
そうでない場合は、無効な日付となり、NULLが返されます。

"月のN回目の指定した曜日の日"
// 仮に5週目を選んだ場合、指定した曜日の日は翌月になる可能性があります。
// その場合は、値をnullにします。
IF DATETRUNC('month', [N回目の指定した曜日の日 - Helper Calc 2-2]) <> DATETRUNC('month', [現在の月の最初の日]) THEN
    NULL
ELSE
    [N回目の指定した曜日の日 - Helper Calc 2-2]
END

最後に、これらの計算について補足があります。
これらはいずれも当月のみを対象としています。
前月や翌月を調べる必要がある場合は、参照する計算フィールドを「前」に入れ替えるだけです。

その他

最後に、DecadeとCenturyについて説明します。

decadeは、10年ごとの切り捨て単位です。
　例: 2021/11/01 -> 2021
Centuryは、100年ごとの切り捨て単位です。日本語で言う「世紀」です。
　例: 2021/11/01 -> 2000

残念ながら、これらにはDATETRUNCオプションがありませんので、自分で切り捨てを行う必要があります。
10年単位では、実際には年の最後の数字を削除して0に置き換えるだけです。
そのためには、年を10で割って整数部分に切り詰め、10を掛けます。

例えば、「2015」という年を考えてみましょう。
10で割ると、201.5となります。次に、整数部分に切り捨てると、201となります。
最後に10を掛けて2010となります。

Centuryも、100で割ったり掛けたりする以外はまったく同じです。
また、必要に応じて、1000を使ってmillenniumについても同じことをすることもできます。

// decade(10年)の単位に切り捨てる
INT(YEAR([日付])/10)*10

// century(100年)の単位に切り捨てる
INT(YEAR([日付])/100)*100

ワークブック

ワークブックはこちらからダウンロードできます。

ワークブックには、利用可能なすべての計算フィールドの一覧表があります。
フィルターを選択すると、表には計算フィールドの名前とフォルダが表示されます。
ワークブックをダウンロードして、計算フィールド（および依存関係にあるもの）をコピーし、
自分のワークブックに貼り付けることができます。

すべての計算フィールドは適切にコメント化されているため、
フィールドの実行内容と理由を簡単に理解できるようになっています。

Thanks:
Header image by rawpixel.com (Freepik.com)