Tableau Prepの作業を変えるTips集１２ ー日付の処理、日数や時間の算出ー
Tableau Prepユーザー会のNakajima2です。
Japan Preppin Data FamメンバーのPrep Tips集をご紹介します。
前回から2回に渡り、メンバー勉強会でもたびたび話題になる数値や日付の処理についてのTipsです。
いろいろな関数があるけど、ちょっと使い方 他のアプリと違っていて分からない という場合の参考になれば！
第１２回目は、日付関数、日付の処理 と 日数、時間の算出 ー割り算、余りの利用ー に関する2つのTipsです。
Prep Tips （２３） ： 日付関数、日付の処理
Tableau Desktop および Prep の日付関数は、Excelなどの関数と異なる独自仕様のものとなっています。ちょっと覚えないといけない関数です。
実務でも良く使う関数、Preppin Data Famメンバーの勉強会でも、「あれ、どの関数使うの？？」と良く議論になっています（主に筆者が毎回聞いていますね、、）。
年度の表示や、月末月初の日を特定するなど、数学の公式の様に覚えておくと便利。簡単にまとめてみました。
なお、各関数において 'date-part‘ 部分を入力する関数では、次の日付、時間に関する項目が指定出来ます。
‘year、’month’、’day’、’quarter’、’week’、
’hour’、’minute’、’second’、’weekday’ など
１） 文字列を日付に
DATEPARSE
特別にフォーマットされた文字列を日付として返します。
構文 ： DATEPARSE(date_format, date_string)
出力 ： 日付
定義 ： <date_format> 引数は <date_string> フィールドがどのように配置されているかを示します。文字列フィールドはさまざまな順序で配置することができるため、<date_format> は正確に一致する必要があります。詳細については、「フィールドを日付フィールドに変換する」(新しいウィンドウでリンクが開く)を参照してください。
例 ： DATEPARSE('yyyy-MM-dd', "1986-03-25") = #March 25, 1986#
MAKEDATE
構文 ： MAKEDATE(year, month, day)
出力 ： 日付
定義 ： 指定された <year>、<monthg>、<day> から構成される日付値を返します。
例 ： MAKEDATE(1986,3,25) = #1986-03-25#
MAKEDATETIME
構文 ： MAKEDATETIME(date, time)
出力 ： 日時
定義 ： <date> と <time> を組み合わせた日付時刻を返します。日付は、日付、日付時刻、または文字列の種類になることがあります。時刻は日付時刻である必要があります。
例 ： MAKEDATETIME("1899-12-30", #07:59:00#) = #12/30/1899 7:59:00 AM#
MAKEDATETIME([Date], [Time]) = #1/1/2001 6:00:00 AM#
NOW
構文 ： NOW()
出力 ： 日時
定義 ： 現在のローカル システムの日付と時刻を返します。
例 ： NOW() = 1986-03-25 1:08:21 PM
注 ： NOW は引数を取りません。
TODAY
構文 ： TODAY()
出力 ： 日付
定義 ： 現在のローカル システムの日付を返します。
例 ： TODAY() = 1986-03-25
注 ： TODAY は引数を取りません。
２） 日付を文字列、数値に
YEAR
構文 ： YEAR(date)
出力 ： 整数
定義 ： 指定された <date> の年を整数で返します。
MONTH
構文 ： MONTH(date)
出力 ： 整数
定義 ： 指定された <date> の月を整数で返します。
例 ： MONTH(#1986-03-25#) = 3
DAY
日付の日の部分 (1 ～ 31) を整数として返します。
構文 ： DAY(date)
出力 ： 整数
定義 ： 指定された <date> の日の部分を整数として返します。
例 ： Day(#September 22, 2018#) = 22 (2018/9/22 を含む四半期の最初の日)
注: 週と ISO 週では、start_of_week が影響します。ISO 週の始まりは、常に月曜日です。この例のロケールでは、 start_of_week が指定されていない場合、週の始まりは日曜日になります。
DATENAME
指定された日付部分の名前を、個別の文字列として返します。
構文 ： DATENAME(date_part, date, [start_of_week])
出力 ： 文字列
定義 ： <date> の <date_part> を文字列として返します。
例 ： DATENAME('year', #3/25/1986#) = "1986"
DATENAME('month', #1986-03-25#) = "March"
DATEPART
指定された日付部分の名前を、整数として返します。
構文 ： DATEPART(date_part, date, [start_of_week])
出力 ： 整数
定義 ： <date> の <date_part> を整数として返します。
例 ： DATEPART('year', #1986-03-25#) = 1986
DATEPART('month', #1986-03-25#) = 3
３） 日付の加算、差
DATEADD
日付部分 (月、日など) に指定された期間を開始日に加えます。
構文 ： DATEADD(date_part, interval, date)
出力 ： 日付
定義 ： 指定された <date> の指定された <date_part> に、<interval> で指定された数値を追加した日付を返します。例えば、開始日に 3 か月を足したり、12 日間を足したりするなどです。
例 ： DATEADD('week', 1, [due date]) すべての締め切り日を 1 週間延長する
DATEADD('day', 280, #2/20/21#) = #November 27, 2021# 2021 年 2 月 20 日の 280 日後
DATEDIFF
2 つの日付の差を日付部分 (週、年など) の数で返します。
構文 ： DATEDIFF(date_part, date1, date2, [start_of_week])
出力 ： 整数
定義 ： <date_part> の単位で表された、<date1> と <date2n> の差異を返します。例えば、バンドに入った日と抜けた日を引き算して、その人がバンドにいた期間を確認します。
例 ： DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751 1986 年 3 月 25 日と 2021 年 2 月 20 日の間の日数
DATEDIFF('month', [date joined band], [date left band]) その人がバンドに参加していた月数
４） 日付の丸め
DATETRUNC
この関数は、日付の丸め機能と考えることができます。特定の日付を受け取り、希望する特別な条件でその日付の変化形を返します。すべての日付には、日、月、四半期、および年の値が必ずあるため、DATETRUNC は、指定された日付部分までの各日付部分に最小値を設定します。詳細については、例を参照してください。
構文 ： DATETRUNC(date_part, date, [start_of_week])
出力 ： 日付
定義 ： <date_part> で指定された精度で <date> を切り捨てます。この関数は新しい日付を返します。たとえば、月の中旬にある日付を月レベルで丸めた場合、この関数はその月の第 1 日を返します。
例 ： DATETRUNC('day', #9/22/2018#) = #9/22/2018#
DATETRUNC('iso-week', #9/22/2018#) = #9/17/2018# (2018/9/22 を含む週の月曜日)
DATETRUNC(quarter, #9/22/2018#) = #7/1/2018#
操作方法例 ー２） 日付を文字列、数値にー
Preppin Data Famメンバーの komatsu1 さんのTipsです。
日付を文字列、数値に変更する例を利用し、Prepでの操作方法を説明します。
Preppin Data 2024Week20のデータを利用し、曜日を求める課題の対応方法を示します。(C) の方法は、曜日以外の作成でも応用が出来るTipsです。
(A) ネイティブの「日付の変換」を利用する
日付のフィールドで、下図の様に文字列などへのデータ変換が出来ます。
ネイティブ機能を利用した際は、曜日に変更するとそのフィールドが上書きされた形でデータ変換がされます。
(B) DATENAME関数を利用する
上記 (A) の例を利用して、次の関数で計算フィールドを作成する方法です。上の変換後のデータと同じ結果が得られます。
DATENAME('weekday',[Date-Orginal])
(C) CASE + DATEPART を利用する
上記 (A) の例を利用して、CASE文にDATEPART関数を用いた計算フィールドを作成する方法です。上の変換後のデータと同じ結果が得られます。
面倒だけど、1番融通効くのはこのやり方。日本語や省略形もTHENの後ろで自由に設定できますね！
CASE DATEPART('weekday', [Date-Orginal])
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
週番号と日付の丸め 週の開始曜日を指定する
Preppin Data Famメンバーの Ai さんのTipsです。
Preppin Data 2024Week13のデータで週番号を求める際のテクニックです。課題では、週の初めの日を月曜日として週番号を求める必要がありました。
週番号を求める方法は何通りかありますが、ここではDATEPART関数を利用した例です。この関数で、構文で記載する
DATEPART(date_part, date, [start_of_week])
中の date_part と [start_of_week] の調整方法がポイントになります。
通常のDATEPART関数の設定は、次の通りです。週の開始曜日は、デフォルトで日曜日になります。
＊次の式で、上段 [ ] 内は、後ほど示すPrep画面のフィールド名です
[Week Number Datepart]
DATEPART('week',[Sales Date])
Preppin Dataの課題となっている週の開始曜日を月曜日にするために、date_part と [start_of_week]) の設定をした例を示します。
[Week Number Datepart_ISO-week]
DATEPART('iso-week',[Sales Date])
[Week Number Datepart_Start Monday]
DATEPART('week',[Sales Date],'Monday')
これらの関数 設定を利用した週番号の表示結果が下のようになります。
[weekday] が Sunday になっている列を比較して頂くと、数番号の数字が違っている（月曜日を開始日とした関数は、数値が小さい）ことが判ります。
同様な週の開始日を変更する処理は、今回例として取り上げた DATEPART関数 だけでなく、日付をまるめるために利用する、 DATETRUNC関数でも有効です。
記載例 ： DATETRUNC('week', [Sales Date],’Monday’)
Prepで文字列を日付に変換することをトライすると
Mr.もりた が第３回Prepユーザー会のイベントで、さまざまな形式の文字列から日付データへの変換を確認しています。
Prepでは、文字列から日付に型変換できるタイミングが2つあります。
1） データ接続時
2） クリーニングステップ
多くの文字列の日付表示方法に対し、日付の型に変更可能か？ を確認し、ほとんどの型で日付の型に変更出来ることが分かっています。
一部、令和の年号がついた文字列（3種ほど）は変換難しいようですが、多くの日付を表す文字列はPrepの機能で実行出来るので、業務上などでのメリットが大きいと思います。
Tableau Desktopで同様な日付への型変換を行おうとすると、意外と変換が出来ず複雑な計算式を作って やっと対処した、なんて経験をされた方 多いのではないでしょうか？
Prepはかなり柔軟に日付型への変換を行ってもらえるので、
データの前加工をPrepで対処してからDesktopでViz作り
は、業務効率面でもメリット大きいと感じています。
Prep Tips （２４） ： 日数、時間の算出 ー割り算、余りの利用ー
実務でも良く出くわす週数とその週の何日目かを算出するケース。
時間計算で時間と分、もしくは秒を算出するケースと合わせ、基準となるデータから割り算とその余りを利用して求めることがよくあります。
Prepでの利用方法、少し留意点があります。Preppin Data FamメンバーのMitamuuさんのTipsからまとめてみました。
割り算、余りの算出 留意点
割り算は、DIV関数の利用と四則演算としての記述 双方で対応出来ます。
演算結果の表示は、数値の丸め方で異なります。
DIV （商を求める）
構文 ： DIV(integer1, integer2)
出力 ： 整数
定義 ： <integer1> を <integer2> で割った割り算の整数部分を返します。
例 ： DIV(11,2) = 5
四則演算（割り算）
構文 ： integer1 / integer2
出力 ： 整数、小数
例 ： 11 / 2 = 5.5
余りの算出は、専用の関数がTableauにはありません。ExcelだとMOD関数がありますが、それに変わる同等の機能を有する利用法は、％ が使えます。
％ （余りを求める）
構文 ： integer1 % integer2
出力 ： 整数、小数
定義 ： <integer1> を <integer2> で割った余りの部分を返します。
例 ： 11 % 2 = 1
PreppinData 2024Week05 を始め、勉強会でも何度も同様の議論が出ています。2024Week05では、フライトチケットの入手期日と基準日の月数差を、3ヶ月単位でグループ分けしたい趣旨の課題でした。
例えば 1～12の月数差があって、1-3,4-6,7-9,10-12の月単位（四半期） でグループ分けしたい時、
DIV([月数差]-1,3)
で計算すると商が求められます。
グループ分けには直接関係ありませんが、余りについては、
([月数差])%3
にすることで求めることが出来ます 。
２つの関数で、次のような結果になります。
2024Week05の3ヶ月単位でグループ分は、商の結果を利用し各グループ名称を表示させることが出来ました。条件式で作成した表示方法の例は、以下の通りです。
IF DIV([月数差]-1,3)=0 THEN 'Recent Fliers (less than 3 months since last flight)'
ELSEIF DIV([月数差]-1,3)=1 THEN 'Taking a break (3-6 months since last flight)'
ELSEIF DIV([月数差]-1,3)=2 THEN 'Been away a while (6-9 months since last flight)'
ELSE 'Lapsed (over 9 months since last flight)'
END
[商] のフィールドは参考として表示
おわりに ー日付に関する課題は多い！ー
日付の処理については、日常業務でも利用する機会が多く、またTableauでの処理も工夫を要する点もある内容です。
Prepping Data勉強会でも、頻繁に対処方法の議論が出る話題豊富な課題です。
今回の投稿だけでは、語れないぐらい多くの日付に関する話題がまだまだあります。機会見つけ、継続的にTipsを紹介できればと思っています。
この記事が気に入ったらサポートをしてみませんか？