【Sisense Data Modeling】CreateDate関数で日付項目のハンドリング

シャローム！Sato-Gです。
最近のセルフサービス系のBIは日付の処理が楽だよね。
年月日さえ持っていれば、年、年月などのカラムをわざわざ作らなくても、年、年月の表示をしてくれる。
少し前のBIではカレンダーを作ってたもんだけど（そのほうが便利なこともあったりするんだけどね）、日付項目の自動生成ができれば誰でも簡単に日付を元にした処理が簡単にできるから、今や当たり前の機能になってきている。
もちろん、Sisenseも日付項目をDate型として正しく設定してあげれば、日付関連の処理はとてもラクだ。
そういうわけで今回はSisenseのデータモデリングにおける日付の扱い方について触れておく。

1.データ型の整理とSisenseのデフォルト仕様

1-1.Sisenseのデータ型の話

Sisense内で扱われるデータのデータ型は以下のとおりで、ファイルから取り込まれた場合は自動的にデータ型を判定しているが、データベースからの場合は、データベースの型に応じて型が決定される。但し、データベースによって持っているデータ型は異なるから、それに近いデータ型にして取り込まれるようだ。

1-2.Integer型の日付の取り込み

【Data Modeling】データベースの準備～Generic JDBC Connectorで接続の記事で触れたSQLiteのテーブルに格納されている注文年月日や配送年月日はDate型ではなく、20100101というようなinteger型、つまり数値型になっている。
それはそもそもがSQLiteにはDate型が存在しないせいでもあるんだけど、日付がこんなふうにinteger、varchar2で入ってるなんて当たり前にある。

実際【Data Modeling】データベースの準備～Generic JDBC Connectorで接続でビルドした注文年月日をみてみると、こんなふうにNumberとして見える。

試しにこれを単純にDate型に変換してみるとこうなる。

ありゃりゃ失敗。数値をシリアル値と判断して日付に変換しようとしているのかな…　いや、値が小さすぎる。
シリアル値というのは1900年1月1日を起点として1日を1として加算したものだから（因みにMacだと1904年1月1日を起点にしている）。
BI製品の中には、yyyymmddで入っているNumber型の項目を正しくDate変換してくれるものもあるんだけど、残念ながらSisenseはそこまではやってくれない。
でも大丈夫、簡単に変換できるからね。

2.数値型の日付をDate型に変換

2-1.変換に使用する関数

数値型の日付をDate型に変換するには、関数を使用する必要がある。ここで押さえておきたい関数は4つ。

(1)CreateDate(year,month,day)
year,month,dayを指定して日付にする。
例) CreateDate(2019,6,6) →2019/06/06

(2)ToInt(string)
文字列を整数に変換する。

(3)ToStrlng(object)
数値などを文字列に変換する。

(4)Substring(string,index,length)
文字列のindexの場所からlength分の文字列を抜き出す。
例）Substring(20190606,5,2) →06

見ればわかると思うだけど、あえて解説するとこうなる。
・ToString(数値)
注文年月日はデータベースではintegerで定義されているので、まずは20100101みたいな形で入ってくる。
数値では文字列をカウントすることはできないので、一旦ToStringを使って文字列に変換する。

・Substring(文字列,n,m)
Substringではn左からn番目からm文字分を取り出す。
つまり 20100101の場合

Substring(ToString([注文年月日]),1,4)　＝2010（年）
Substring(ToString([注文年月日]),5,2)　＝01（月）
Substring(ToString([注文年月日]),7,2)　＝01（日）

という結果を得る

・CreateDate(年, 月, 日)
CreateDateはCreateDate(年, 月, 日)で日付に変換する。
年、月、日はint型でなきゃいけないのでToIntでintegerに変換する必要がある。変換された結果、CreateDate(2010,1,1)となり、Date変換されて2010/01/01のようなDate型の値となる。

もし日付に変換したい項目（今回の場合は注文年月日）がtextだったらこうなる。つまりToStringはいらない。

Substring([注文年月日],1,4)　＝2010（年）
Substring([注文年月日],5,2)　＝01（月）
Substring([注文年月日],7,2)　＝01（日）

2-2.カスタム列に実装する

早速実装してみよう。
元の注文年月日カラムはそのままにし、カスタム列に定義する。
Webデータデザイナーで「注文ヘッダ」から「カスタム列を追加」で

カスタム列名を「注文日」とし、以下の数式をセットし、[プレビューボタン]を押すと以下のように正しく変換されたことが確認できる。

CreateDate(
ToInt(Substring(ToString([注文年月日]),1,4)),
ToInt(Substring(ToString([注文年月日]),5,2)),
ToInt(Substring(ToString([注文年月日]),7,2))
)

同じように配送年月日も配送日といカスタム列で定義する。

CreateDate(
ToInt(Substring(ToString([配送年月日]),1,4)),
ToInt(Substring(ToString([配送年月日]),5,2)),
ToInt(Substring(ToString([配送年月日]),7,2))
)

ここでビルドして確認。
ダッシュボードに実装してみるとこうなる。

注文日をカテゴリに追加したバーチャートの場合、注文日は年、四半期、月、週、日、時間でX軸の切替ができる。
まあまあ簡単にできたでしょ？

因みにYYYYMMDDがテキストの場合はこう

CreateDate(
ToInt(Substring([注文年月日],1,4)),
ToInt(Substring([注文年月日],5,2)),
ToInt(Substring([注文年月日],7,2))
)

テキストでYYYY/MM/DDで入っている時はこうなる。
※StrPartsは区切り文字を指定して何番目かを指定する。

CreateDate(
ToInt(StrParts([date],'/', 1)),
ToInt(StrParts([date],'/', 2)),
ToInt(StrParts([date],'/', 3))
)

3.まとめ

CreateDate, ToInteger, ToString, Substringの関数を使って、Date変換する方法を解説した。
Sisenseでは日付、時刻系の関数として
CURRENT〜系
ADD〜系
GET〜系
〜OF〜系
〜DIFF系
などの豊富な関数が準備されている。これらの関数もDate-Time型じゃないと機能しないから、日付のデータはさっさとDate変換しけおいたほうがいい。
上記にリストアップした関数についても追い追い取り上げていくね。

ではまた！