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

いざやってみようとしてすごく苦労したのですが、出来てみると案外、サクッといい感じに可視化できたので、記事にします。ただし、この手順は <a href="https://www.tableau.com/" target="_blank" title="">Tableau</a> が使えることが前提です。

## 解決したい課題

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

- <a href="https://community.tableau.com/thread/151387" target="_blank" title="">FAQ:  Open & Close Dates |Tableau Community</a> 
- <a href="https://kb.tableau.com/articles/HowTo/showing-records-that-fall-within-a-period-of-time?lang=ja-jp" target="_blank" title="">アクティブなレコードを時間経過とともに表示する</a> 

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

## データの形式

<a href="http://livedoor.blogimg.jp/kobakou/imgs/5/9/59c6f2e1.png" title="datasample" target="_blank"><img src="http://livedoor.blogimg.jp/kobakou/imgs/5/9/59c6f2e1-s.png" width="394" height="312" border="0" alt="datasample" hspace="5" class="pict" align="left"  /></a><br clear=all>

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

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

```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のデータピボット機能を使って、整形します。

<a href="http://livedoor.blogimg.jp/kobakou/imgs/e/7/e778ba4c.png" title="transformbypivot" target="_blank"><img src="http://livedoor.blogimg.jp/kobakou/imgs/e/7/e778ba4c-s.png" width="349" height="262" border="0" alt="transformbypivot" hspace="5" class="pict" align="left"  /></a><br clear=all>

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

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

- 一番重要な `IssueCnt` です。Pivotされた行に対しOpenされたのなら +1 Closeされたのなら -1 とします。<pre> CASE [Action] WHEN 'Create At' THEN INT(1) else INT(-1) END </pre> でいいでしょう。このIssueCntを日付でSumしながらRunningTotalしていけばアクティブな件数が算出できます。
- Close日がNullのお手当: 未来に日付にしておく. <pre>ISNULL([DATE), TODAY()) や DATEADD('day', 1, today())</pre> などでいいでしょう
- `isClosed` : Closeしたものだけを見たいケースもあるでしょうから、 <pre>NOT(ISNULL([DATE])) </pre>でフラグを作っておきます。↓では、isClosed==Tureのみを表示しています

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

<a href="http://livedoor.blogimg.jp/kobakou/imgs/c/6/c66024a6.png" title="ActiveIssues" target="_blank"><img src="http://livedoor.blogimg.jp/kobakou/imgs/c/6/c66024a6-s.png" width="1200" height="551" border="0" alt="ActiveIssues" hspace="5" class="pict" align="left"  /></a><br clear=all>
<a href="https://public.tableau.com/views/issues/ActiveIssues?:embed=y&:display_count=yes" target="_blank" title="">tableau publicにもアップロードしました</a>

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で同じ列に並んでいないなど不満も...

<a href="http://livedoor.blogimg.jp/kobakou/imgs/3/7/377b4e8d.png" title="excelsample" target="_blank"><img src="http://livedoor.blogimg.jp/kobakou/imgs/3/7/377b4e8d-s.png" width="348" height="219" border="0" alt="excelsample" hspace="5" class="pict" align="left"  /></a><br clear=all>

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