DATEDIFF関数は2つの日付間の差を求める際に利用され、例えば日数や月数、年数などを容易に計算できる関数です。
例えば購入から何日経過したかを追跡したり、顧客の年齢を自動で算出したりといったことが可能になり、日付を絡めた詳細な分析が容易になります。
TableauのDATEDIFF基本構文
DATEDIFF(date_part, date1, date2, [start_of_week])
date_partで差を計算したい日付単位を指定します(例:’day’, ‘week’, ‘month’, ‘year’など)。
date1とdate2はそれぞれ比較対象の日付を指し、[start_of_week]は週の始まりを指定するオプションです。
DATEDIFF関数はSQLやエクセルにも同じ役割の関数がありますが、TableauのDATEDIFF関数は他のツールとは定義が異なります。
本コラムではTableauのDATEDIFF関数の他ツールとは異なる定義について解説します。
DATEDIFF関数を使ったものの思った値が返ってこなかった人や、Tableauで日付の差分を出したい人はご参照ください。
TableauのDATEDIFF関数は日付の切り捨てに注意が必要
2023/11/10から2024/11/9は、1年間は経過していないので、「年単位」の場合は「0」、「月単位」の場合は「11」が返ってきてほしいです。
エクセルでのDATEDIF関数での戻り値はこちらです。欲しい値がとれています。
こちらは、先ほどと同じ日付をTableauのDATEDIFF関数で出したものです。欲しい値に+1した結果が返ってきています。
TableauのDATEDIFF関数の定義は、2つの日付を指定した「年」や「月」単位に切り捨てしてから2つの日付の差分を出します。
なのでdate_partに「year」を指定した場合は、2023年と2024年に切り捨てしてから差分を出すので、1年経過していなくても戻り値が「1」に、
date_partに「month」を指定した場合は、日付が2023/11と2024/11に切り捨てしてから差分を出すので、12ヶ月経過していなくても戻り値が「12」が返ってきます。
回避策
Tableauでは定義が違うことは理解しましたが、エクセルのように経過年数(月数)を出したい場合は、以下の計算式を参照ください。
こちらの計算式はSalesforceの公式ヘルプ「Calculating Difference in Dates Using DATEDIFF Returns Unexpected Results」を参考にしながら作成しました。
開始日より終了日が後になることが前提の計算式です。マイナス数値には対応しておりませんのでご注意ください。
【年】
IF
DATEPART('month',[開始日])=DATEPART('month',[終了日])
AND
DATEPART('day',[開始日])>DATEPART('day',[終了日])
THEN
DATEDIFF('year',[開始日],[終了日])-1
ELSEIF
DATEPART('month',[開始日])>DATEPART('month',[終了日])
THEN
DATEDIFF('year',[開始日],[終了日])-1
ELSE
DATEDIFF('year',[開始日],[終了日])
END
【月】
IF
DATEPART('day',[開始日])>DATEPART('day',[終了日])
THEN
DATEDIFF('month',[開始日],[終了日])-1
ELSE
DATEDIFF('month',[開始日],[終了日])
END
実践的なDATEDIFF関数の活用例
先ほどの計算式を応用したDATEDIFF関数の活用例を2つご紹介します。
❶年齢計算
生年月日から現時点で何歳か計算したいと思います。TODAY()には執筆日の2024/11/13が該当します。
〜計算式〜
〜結果〜
1行目と3行目は日付の切り捨てを回避した結果が返ってきています。
〜注意点〜
・メジャーには「属性」=ATTR()を選択しています。「属性」はグループ内のすべての行が同じ値を持つ場合にのみ指定された式の値を返すものです。
もし同じ生年月日が2行あったとしても年齢は同じなので、メジャーは「属性」を選択しました。「合計」のままだと行数分合計してしまうので注意が必要です。
・TODAY()関数・NOW()関数はUTCタイムゾーンを返します。
今回の年齢の計算式には考慮していませんが、日本時間(JST)にするにはNOW()から-9時間するなどして対処しましょう。
詳しくはこちらのSalesforceの公式ヘルプをご参照ください。
❷会員登録日から購入日まで何ヶ月かかったか計算
〜計算式〜
〜結果〜
2行目は日付の切り捨てを回避した結果が返ってきています。
まとめ
TableauのDATEDIFF関数は指定した日付の単位で切り捨てされてから差分を出す仕様のため、意図しない結果が返ってくることがあるので注意しましょう。
日付から経過年数や経過月数を出したい場合は、計算式を使って回避することが可能です。
TableauのヘルプにもDATEDIFFの定義は「<date_part> の単位で表された、<date1> と <date2> の差異を返します。」と記載がありますので、あれ?と疑問に思うことがあればまずはヘルプで定義を確認することをおすすめします。