いざやってみようとしてすごく苦労したのですが、出来てみると案外、サクッといい感じに可視化できたので、記事にします。ただし、この手順は Tableau が使えることが前提です。
解決したい課題
想定しているのは、レコードが1行毎入っていて、それぞれにOpen、Close日付が記録されているようなもの。そこから、日々どれだけOpenされ、Closeされ、Activeなものがどれだけ増減しているかを時系列でおいたいというテーマ。結構よくある課題のようで、Communitiyでも質問を見かけ、FAQやら、公式ナレッジの記事も見つけました。
が、残念ながら、公式ナレッジの手順はあまりに複雑で、かつ、月や週、日と具合に時間軸のレンジを変えるのも難しい方法だったので、私は断念し、この方法でうまくいきました。
データの形式
データとしてはこんな感じ。この例では、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 とします。でいいでしょう。このIssueCntを日付でSumしながらRunningTotalしていけばアクティブな件数が算出できます。CASE [Action] WHEN 'Create At' THEN INT(1) else INT(-1) END - Close日がNullのお手当: 未来に日付にしておく. などでいいでしょう
ISNULL([DATE), TODAY()) や DATEADD('day', 1, today()) -
isClosed: Closeしたものだけを見たいケースもあるでしょうから、でフラグを作っておきます。↓では、isClosed==Tureのみを表示していますNOT(ISNULL([DATE])) 出来上がったVizがこちら
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でも、もっとスマートな方法を知っている方がいれば、ぜひフォローください。