[tableau] Open/Closeの表から、アクティブなレコードを時間経過とともに表示する、もう一つの方法

いざやってみようとしてすごく苦労したのですが、出来てみると案外、サクッといい感じに可視化できたので、記事にします。ただし、この手順は Tableau が使えることが前提です。

解決したい課題

想定しているのは、レコードが1行毎入っていて、それぞれにOpen、Close日付が記録されているようなもの。そこから、日々どれだけOpenされ、Closeされ、Activeなものがどれだけ増減しているかを時系列でおいたいというテーマ。結構よくある課題のようで、Communitiyでも質問を見かけ、FAQやら、公式ナレッジの記事も見つけました。

• FAQ: Open & Close Dates |Tableau Community
• アクティブなレコードを時間経過とともに表示する

が、残念ながら、公式ナレッジの手順はあまりに複雑で、かつ、月や週、日と具合に時間軸のレンジを変えるのも難しい方法だったので、私は断念し、この方法でうまくいきました。

データの形式

データとしてはこんな感じ。この例では、Titleとかを無名化していますが、Issueのタイトルが入っていたり、人の名前が入っていたりする感じで、よく見ると思います。

ちなみに、今回私がやりたかったのは、GithubのIssuesの状況の可視化だったので、以下のようにして Github の API を使って Pythonでリストを作りました。

from github import Github
import pandas as pd

# or using an access token
g = Github("")

for _repo in g.get_organization('your-org').get_repos(type='private'):
    if _repo.name == 'your-repo':
        repo = _repo
        break

issueslist = []
for _issue in repo.get_issues(state=('open')):
_issue.labels, _issue.created_at, _issue.closed_at))
    issueslist.append([_issue.number, _issue.title, _issue.labels, _issue.created_at, _issue.closed_at])
for _issue in repo.get_issues(state=('closed')):
_issue.labels, _issue.created_at, _issue.closed_at))
    issueslist.append([_issue.number, _issue.title, _issue.labels, _issue.created_at, _issue.closed_at])

issuedf = pd.DataFrame(issueslist, columns=['id','title','labels','create_at','closed_at'])
issuedf.to_csv('issues.tsv', sep='\t', index=False)

cf. https://gist.github.com/kobakou/e54fc08464cdb874697bcc292187c262

Tableauでの可視化の戦略

要するにOpenしたときに +1 Closeしたときに -1 して、それを時系列データにできればよい、ということに気付き、Tableauのデータピボット機能を使って、整形します。

ここに気付ければ勝ちでした。可視化に辺りいくつか計算フィールドを作っておきます

いくつか計算フィールドを作ります。

• 一番重要な IssueCnt です。Pivotされた行に対しOpenされたのなら +1 Closeされたのなら -1 とします。

   CASE [Action] WHEN 'Create At' THEN INT(1) else INT(-1) END 

  でいいでしょう。このIssueCntを日付でSumしながらRunningTotalしていけばアクティブな件数が算出できます。
• Close日がNullのお手当: 未来に日付にしておく.

  ISNULL([DATE), TODAY()) や DATEADD('day', 1, today())

  などでいいでしょう

• isClosed : Closeしたものだけを見たいケースもあるでしょうから、

  NOT(ISNULL([DATE])) 

  でフラグを作っておきます。↓では、isClosed==Tureのみを表示しています

• 出来上がったVizがこちら

tableau publicにもアップロードしました

Openの状況、Closeの状況、そして残Activeの状況が一度に見れて、いい感じのチャートになりました。もちろん、もともとに行にラベルや担当者といったフィールドが付いていたら、それを使ってフィルタしたり、ディメンジョンを切ったりできます。

通常のガントチャートなどはもともとのTransfromしない状態の方が作りやすいので、同じデータソースを参照しつつ、Datasetを別に登録してやると使い勝手がよさそうです。

今回の教訓は、可視化の際には、データのカタチを目的にあわせておくのが簡単、ということですね。tableauの計算フィールドやテーブル関数を駆使していろいろ加工はできる（ような）のですが、その手前のSQLでの読み出しやTableauのData読み込みツールの段階で、使いやすいカタチにしておくのが実際は近道ですし、可読性なんかも良くなると思います。そしてもう一つは記事にするのメンドウ 笑

..そして、その後さらに英語の記事を見つけました！これこれ。http://onenumber.biz/blog-1/2018/3/19/how-do-you-calculate-headcount-with-only-a-start-date-and-end-date-in-tableau

Excelの場合

Excelでも似たことは実現できましたので、せっかくなので言及しておきます。Excelの場合も、ピボットテーブルを使って 日付/Open（個数） と 日付/Close（個数）をそれぞれ表を作ります。ピボットテーブルをする際に、「データのないアイテムを表示する」というオプションがあるので、これをチェックします。こうすることで、OpenとCloseの2つのピボットテーブルを横に並べてくっつけられるので、あとは計算式を作って、日々のActiveを計算しグラフにすることができました。が、、あまりに手作業が多い..毎週この表作るの無理ということで別の手段を探していました。またグラフもOpenとCloseで同じ列に並んでいないなど不満も...

もし、みんなが使えるExcelでも、もっとスマートな方法を知っている方がいれば、ぜひフォローください。