Qlik Senseの日時データの取り扱い
更新日：11月1日
「時」「分」「秒」「日（7:00始まり）」「10分単位に丸める」などをまとめてご紹介
時刻を含む日時項目の基本セットを作るサンプルスクリプトをご紹介
アクセスログなど時分秒単位のデータを分析することも増えてきました。「時刻」のデータも日付同様に「時間単位」や「分単位」で集計が欠かせません。
今回は日時データを加工して「**時台」や「日をまたいで1日とする（7:00～6:59 等）」など様々な時刻に関する項目を作成するパターンをメモしておきます。（※サンプルのロードスクリプト付き）
まずは、Excelで日時データを取り込み、時刻に関するデータ分析の基本セットを作りました。
【時刻に関する基本セット】
▼時：Hour関数
これは簡単です。Hour関数を使います。
例）Hour( '2021/1/1 09:10:23' ) は、9 を返します
Hour(日時)'時台' を連結することで「9時台」のようにできます。
例）Hour( '2021/1/1 09:10:23' )&'時台' は、9時台 を返します
Hour(日時)&'時台'▼分：Minute関数
これも簡単です。Minute関数を使います。
例）Minute( '2021/1/1 09:10:23' ) は、10 を返します
Minute(日付)▼秒：Second関数
これも簡単です。Second関数を使います。
例）Second( '2021/1/1 09:10:23' ) は、23 を返します
Second(日付) ▼時分：Hour関数とMinute関数
時と分を&で連結します。
例）Hour( '2021/1/1 09:10:23' ) & ':' & Minute( '2021/1/1 09:10:23' ) は、9:10 を返します
Hour(日時) &':'& Second(日時)Timestamp関数で書式を整えます。
Timestamp(Hour(日時) &':'& Second(日時),'hh:mm')ここからは、単純な関数だけでは実現できない少し複雑な「開始時間をずらした日付」や「10分単位」の項目です。
▼1日の開始時刻をずらす（7:00はじまり）：
日時から7時間ずらして日付を計算します。時刻は1を24で割ったシリアル値で表されているため、1/24 は1時間を表します。0.125 は3時間となり、7時間は 1/24*7=0.291666… となります。
例）'2021/9/10 6:59:59' -0.125 は、2021/9/10 3:59:59 を返します
例）'2021/9/10 6:59:59' -0.25 は、2021/9/10 0:59:59 を返します
例）'2021/9/10 6:59:59' -0.2916666 は、2021/9/9 23:59:59 を返します
例）'2021/9/10 7:00:00' -0.2916666 は、2021/9/10 00:00:00 を返します
上記のように返ってきた値をDate関数で（YYYY/MM/DD）を取ればOKです。ただし、この際に注意が必要なのは、Date関数は書式変換の関数なので内部のシリアル値は異なる値のままです。よって、見た目は同じ値でもなぜか画面上では複数の値になってしまうという事象となります。
これに対応する方法が2通り記載します。
①Floor関数 でシリアル値の小数点以下を切り捨てることで日付単位で集約する。
Date( Floor( 日時-( 0.2916666 ) ) )例）Floor( 44448.964 ) は、44448 を返します。
例）Floor( 44449.012) は、44449 を返します。
②Text関数 で文字列に変換して集約する。
Text( Date( 日時-( 0.2916666 ) ) )＜Date関数で複数行になった項目と、それぞれFloor関数、Text関数で対応した項目＞
▼10分単位で丸める：
考え方としては「１日の開始時間をずらす」のと同じやり方となります。日単位ではなく分単位での計算となります。1/24は1時間となるのでさらに6で割って10分単位とします。（1/24/6＝1/144＝0.0694444...）
例）Timestamp(Floor('2021/9/10 6:59:59', 1/144)) は、2021/9/10 6:50:00 を返します
例）Timestamp(Floor('2021/9/10 7:04:59', 1/144)) は、2021/9/10 7:00:00 を返します
Timestamp(Floor([日時],1/144))※時刻を丸める際に切り下げではなく、四捨五入や切り上げとする場合は、Floor 関数ではなく、Round 関数（四捨五入）やCeil 関数（切り上げ）を使用します。
【補足・参考】
日付シリアル値の計算を利用するサンプル数式ですが、7時開始の計算として「0.2916666」を引き算しました。厳密には「1/24*7」を引き算するのが良いかもしれないですが、おそらく割り切れない数値なので扱いやすくするため「0.2916666」という数値で計算しました。どうやら浮動小数点の仕様によって、Floor関数の結果に影響が出ることがあるらしいです。下図のを見ると「2021/9/10 7:00:00」のシリアル値が異なっているのが確認できます。
こういった点も踏まえて、Text() 関数で文字列変換する手法はよく使っています。また、10分単位の丸め処理も既述のサンプル数式を基にして10分で切り下げつつ書式で秒を '00' とし、文字列変換しています。
text(Timestamp(Floor(日時,1/144),'YYYY/MM/DD hh:mm:00'))★時刻データの基本セット
時系列の基本セットを生成するロードスクリプトのサンプルをメモしておきます。こうやって並べて記載するとよく分かりませんが、コピペして修正しながら使えると思います。
日時,
Hour([日時]) as 時
Hour([日時])&'時台' as 時間帯
Minute([日時]) as 分
Second([日時]) as 秒
Timestamp(Hour(日時) &':'& Minute(日時),'hh:mm') as 時分
Text(Date(日時-(0.2916666))) as 日付(7時開始_文字列)
Date(Floor(日時-(0.2916666))) as 日付(7時開始_日付)
text(Timestamp(Floor(日時,0.0694444),'YYYY/MM/DD hh:mm:00')) as 時刻(10分単位)
おわり
Comments