ThoughtSpotTipsで複数の日付項目のカウントを一つのグラフにする方法をご紹介します

 

AkimasaKajitaniです。

今回は、あるデータに複数の日付項目があり、それぞれの項目に対して日付のカウントを取り、一つのグラフに推移として表示したいときの方法をご紹介します。意外とこういうことやりたいことは多いと思いますが、BIツールが苦手とする内容です。

今回のサンプルデータ

某サンプルのスーパーストアのデータを使っていきます。これにはオーダー日と出荷日の2つの日付項目があり、それぞれ個別にカウントし、推移グラフにしたい、とします。

元データのイメージは以下のようになります。

ポイント

実は、このようなケースでは、データソース時点で何らかの工夫が必要になります。単純にキーワードや関数でどうにかするのは難しいです（他のBIツールでも（Tableau、PowerBI等）、単純にはいきません）。

考え方としては、各日付項目（オーダー日、出荷日）ごとに日付のカウントを行いビューとして保存したものをまず作成し、その2つのビューを日付項目同士で結合して一つのテーブル（モデル）としてまとめる必要があります（BIツールによっては、日付のカウントの事前集計は必要のないものもあります）。

この時、カレンダーテーブルを使う場合は、カレンダーテーブルの日付を軸として結合していく形になります。カレンダーテーブルなしの場合は、結合後に計算式で項目を一つ作る必要があります。

少し手順をまとめてみます。

• 共通手順
  • オーダー日でカウントしてビューとして保存
  • 出荷日でカウントしてビューとして保存
• カレンダーテーブルを使わない場合
  • カウントしたテーブルを結合する時、完全外部結合でJOINする
  • 結合後、日付項目を作成する、この時、いずれかのカウントしたテーブルの日付のうちNullではないものを採用するような計算式を作成する
• カレンダーテーブルを使う場合
  • カレンダーテーブルとして、オーダー日、出荷日すべてを含んだテーブルを用意する
  • カレンダーテーブルとカウントしたテーブルは１：１でInner-JOINする

 

実際の手順

今回h、カレンダーテーブルを使わない方法で進めてみます。

１．オーダー日で検索し、ビューとして保存する

対象のデータソースに対して、以下のようなキーワードで検索します。

これによって以下のように可視化されます。

三点メニューから「ビューとして保存する」を選択すると、検索結果をビューとして保存できます。

 

わかりやすい名前をつけて保存しましょう。

２．出荷日で検索し、ビューとして保存する

次に、対象のデータソースに対して、以下のようなキーワードで検索します。

これによって以下のように可視化されます。

３．２つのビューを結合しモデルを作成する

次に、データワークスペースを開きます。

今回以下のような形で保存しています。

データワークスペースで、新規作成から「モデル」を選択します。

次に、通常のデーターベースなどの接続であれば、「エディタを使用したモデリング」を選択します。Analyst Studioのデータセットの場合は「ThoughtSpotデータセット」を選択します。

データモデルエディターでは、作成したビューを２つドラッグ＆ドロップしていきます。

その後、マウスオーバーしたときに出てくる「＋」ボタンをドラッグ＆ドロップします。

 

これにより、結合の方法を決定できるので、以下のように設定します。

特にJoin Typeは「フルアウター」、カーディナリティは事前集計しているので、１：１とします。

あとは、必要な列を追加していきます。

次に、数式を作成していきます。これは、月単位のデータくらいまで集計している場合、どちらかの日付のレコードが存在しない、ということはないのですが、日付単位で作成するような場合、いずれかのイベントが発生しない、ということは十分ありうるので、Nullを計算式で埋める必要があります。

つまり、具体的には、以下のように2025/01/11が出荷日の方に存在しておらず、オーダー日の方に2025/01/12がない場合、グラフに表示する場合に、欠損が発生してしまいます。

このため、両方の日付を使って新しい軸となる項目を作成する必要があります。このためには、数式タブにて、新規の数式を作成します。基本的にifnull関数で簡単に作成が可能です。

 

 

ifnull ( Month(出荷日) , Month(オーダー日) )

 

なお、このようにDATE列を作ってしまえば、あとはもともとのビューに存在していたMonth(出荷日)、Month(オーダー日)は削除しても構いません。

また、アグリゲーションが「COUNT」になっている場合は「SUM」に変更しましょう。

 

４．可視化する

それでは最後に可視化していきましょう。先ほど作成したモデルで検索を行います。検索画面では、以下のような項目になっているかと思います。モデルの方で計算式を作ると、通常の日付などとして認識されます（計算式の方に入りません）。

あとは、以下のようにキーワード検索を行っていきます。

グラフが２軸となっていますね・・・。軸が１つのほうが見やすいので、１軸に変更したほうが良いです。

２軸になってしまったグラフは、グラフの軸をクリックし、「グループ」から同じ軸にしたい項目名を選択します。

 

これで完成です。

おまけ

SQLビューを使って、SQL文を直接書くことも可能です。SQLビューは、データワークスペースの新規作成のところから「SQLビュー」を選択することで利用可能です。

 

ここでは完全にSQLを書く形になります。

 

SELECT t1.*,t2.*,COALESCE(t1.DATE_Order,t2.DATE_Ship) AS DATE_Common FROM
(SELECT "オーダー日" AS DATE_Order,COUNT("オーダー日") AS COUNT_Order FROM DBNAME.SAMPLESUPERSTORE.ORDERS Group By "オーダー日") AS t1
FULL OUTER JOIN 
(SELECT "出荷日" AS DATE_Ship,COUNT("出荷日") AS COUNT_Ship FROM DBNAME.SAMPLESUPERSTORE.ORDERS Group By "出荷日") AS t2
ON t1.DATE_Order = t2.DATE_Ship

 

されにこれをモデルにしていくことも可能です（モデルにしないとライブボードフィルタが使えないので、積極的にモデル化しましょう）。

 

※ThoughtSpot バージョン 10.12.0.cl-105時点の情報です