【Tableau】十大表計算-完整解讀版
之前寫lod部落格，特別是完整的分析了官方的「15大詳細級別表達式案例」，獲得了很多人的感謝;在整理表計算的過程中，我想把表計算的10大案例，也用中文的方式完整復原，更接近於初學者的理解。 同時穿插講解表計算主要表達式的語法和用法。
- 英文官方連結：Top 10 Tableau table calculations2015/01/15 by ELLIE FIELDS
- 中文官方連結：
本文的必要性，在於軟體本身的與時俱進：
1、官方在提供10大表計算case時，還不支援臨時計算（Tableau 9+），因此官方指南都是先做計算字段，再展現圖形;有了臨時計算，推薦大家先在視圖中寫臨時計算，反覆修改、證偽，再去轉化為固定的自定義計算字段保存;
2、早期的部分表計算有了更簡單的表計算函數代替，比如之前使用完整的window_sum（expression，first（），last（））計算整個視窗聚合，可以省略後面的參數，也可以直接用total（）代替;在使用 -index（） 定位和當前位置偏移的時候，也可以直接使用first（），更容易理解;
3、部分手錶計算，可以使用快速表計算代替，比如TC6中計算權重的步驟，可以用快速表計算-總額百分比加速分析過程;
為了説明大家學習，我的每個案例解說結尾，都可以下載我的詳細步驟檔，以及public分享連結。
表計算基礎知識
表計算依賴兩種類型的欄位：尋址欄位和分區欄位。 瞭解表計算的關鍵是弄清楚這些欄位的工作方式。
顧名思義，分區字段執行以下操作：將數據分成多個單獨的存儲桶，並對每個存儲桶執行計算。 尋址欄位則用於定義執行計算的「方向」。
難度係數：
1、自參考日期開始的百分比變化（參數、window_表計算）
這個題目的數據是多個證券板的收盤價，目的是生成每天指定參考日期的百分比變化，比如指定2018/11/28，視圖就是每天相對於2018/11/28的漲跌百分比。 如下圖所示：
使用一個表示參考日期的參數和 WINDOW_MAX 函數來獲得參考日期當日的收盤價，這樣就能計算股票的相對收益。
我們用這個案例，完整的講解如何思考有關表計算的分析。
分析過程：
- 數據中包含了三個sticker，可以理解為上海、深圳、科創板三種不同的股票板;我們要返回每天的股票收盤價相當於參考日期的百分比，為了理解方便，也可以先理解為每天收盤價相對於參考日收盤價的差異（從差異到差異百分比僅僅一步之遙）。
- 如果是返回相當於2018/11/28的差異，則用 當天的收盤價減去2018/11/28的收盤價。
- 思考方式：按照我們之前「如何選擇計算方法」的說明，我們可以問一下：
"視圖中是否包含了計算需要的所有數據？" ——是的。
計算所有的數據點，都在視圖中，因此詳細級別無需調整，僅需要通過創建中間輔助欄位，實現我們的差異或差異百分比。
接下來，我們開始從計算，解決這個問題：
第一步 返回參考日的收盤價（參數+if判斷）
如果要從一堆數據中提取數據，同時我們又明確地知道提取的規則，首先考慮地就是用if函數邏輯化這個規則。 在這裡：當 日期=參考日期，返回當天的收盤價，否則返回null （後期大家可以嘗試返回0看看效果是否不同）。 創建一個日期參數，獲得參考日的日期。 然後提取參考日的收盤價，函數如下：
IF (max([Date]) = [Enter Reference Date:])
THEN SUM([Adj Close]) ELSE NULL END
這裡需要注意，date日期需要聚合，否則就會報錯。 維度的聚合方式有計數、最大最小值，這裡每天只對應一個日期，我們使用最大值或者最小值聚合均可。
此時，如果我們把這個收盤價加入視圖，和之前的收盤價放在一起，效果如下：
第二步 如何對比每天的收盤價和參考日收盤價的差異？？ （快速表計算嘗試）
難點就在於這裏，我們想讓折線上的每個點計算和下面參考日收盤價的差異;為此，我們需要把下面的三個點，轉化為三條線——也就是每天的詳細級別上，收盤價都可以和參考日收盤價計算差異。
表計算可以幫我們返回多個數值，我們可以嘗試一下快速表計算，比如匯總running_sum，這時點後面成了一條線，說明思考方向是可行的。 如果我們把這幾步結合起來，可以看到以下效果：
第三步 嘗試手錶計算（window表計算）
上面快速表計算不行，但是方向無誤，我們嘗試其他表計算返回完整的一條線。 表計算有一個非常好用的表計算函數是 視窗函數，它可以指定視窗的起始位置，可以使用 first和last調整。
WINDOW_MAX(expression, [start, end])
返回視窗中表達式的最大值。 視窗用與當前行的偏移定義。
使用 FIRST（）+n 和 LAST（）-n 表 示與分區中第一行或最後一行的偏移。
如果省略了開頭和結尾，則使用整個分區。
我們可以用下面的圖片說明以下它的用法，預設window_max（ sum[Sales]）如果不加偏移參數，就會返回整個視窗的最大值。
在我們本案關於收盤價的分析中，我們要把參考日收盤價擴展到整個視窗，就可以理解為如何從視窗範圍返回單點數值，因為只有一個數值，我們可以使用window_avg，window_max多種方式。 在有了整條線的數據后，我們就可以計算差異，如下：
到這一步，就接近尾聲了，如果要計算百分比變化，我們就把差異增加一步，計算：
（SUM（[Adj Close]）-WINDOW_AVG（[參考日收盤價]））/WINDOW_AVG（[參考日收盤價]）
再調整以下數據軸的格式和顏色，就是開篇我們展示的效果了。
總結以下這個案例：
- 分析問題時，首先想什麼情況下考慮使用表計算？ 兩類情形：涉及到行內計算、回歸計算、移動平均等特殊計算時;以及在視圖中包含了所有需要的數據時。 這在「如何選擇計算」時分享過。
- 分析的過程要分解為可以理解的小步驟，避免一蹴而就;Tableau提供的"即席計算"可以很好的説明我們隨時驗證假設，隨時保存計算。
我的完整的參考檔，可以從這裡下載：TC1-Relative-Stock-Return 股票相對變化.twbx
或者參考我的Tableau Public主頁內容連結。
2、公共基準（《玩具總動員》）（index索引表計算）
本案的數據是三部電影的從上映到最後每天的票房收入，我們希望忽略上映的絕對日期，只反映相對日期，比如上次第一周、第二周、第N周的票房收入。 從分析上，我就需要我們把絕對日期，調整為相對日期顯示。 兩種顯示的方式對比如下：
如果我們看一下上面兩個視圖的「交叉表」就會發現，一個是絕對時間，另一個是相對時間，如何把絕對時間轉化為相對時間呢？ 我們可以把絕對時間視為維度，把相對時間視為是絕對時間的排序，即最早的時間為1，其次為2，以此類推。
在「如何選擇計算」一文中，當遇到排序、移動平均、回歸計算等問題時，就要選擇表計算。 表計算片關於排序有index、rank兩種方式，區別在於 rank需要是基於聚合表達式的排序，比如rank（ sum[sales] ），而index完全是基於欄位表計算的分區和尋址，因此沒有參數。 我們這裡是基於電影分區、時間尋址，在原來的基礎上添加索引即可。
index（ ） 傳回分區中當前行的索引，不包含與值有關的任何排序。
RANK(expression, ['asc' | 'desc']） 傳回分區中當前行的標準競爭排名。
問題就改為了：如何通過表計算，把每部電影的放映時間，按照時間日期改為獨立的相對時間。 即story 1的放映時間從1開始排序，story 2也是如此，這相對於約束了表計算的分區依據（電影和時間）和尋址順序（放映時間）。 我們看一下交叉表和添加了排序的交叉表。
結果是以上映的周為單位，因此我們也使用周來分析。
有個這些必要的數據，剩下的就是如何把數據轉化為可視化圖形了。
這裡需要特別注意的是，同樣的數據點，只是改變可視化樣式（從交叉表改為圖形），數據的詳細級別不會改變——依然是每部電影上映后各周的票房收入。 為此，我們不應該把視圖中的「絕對日期」移除，而應該換一個位置，從視圖行/列移到標記的"詳細級別"上，而將索引的index拖到行作為維度，然後表標記改為線即可。 此時展示的是：各個電影在開映后各周的票房收入，如果要看累計，添加快速表計算匯總，計算依據改為date即可。
使用Tableau的INDEX（） 函數，可輕鬆計算自首映開始的星期數。 在這個例子中，您按電影分區，按天數尋址。
總結：
https://www.zhihu.com/video/1100681497660436480
- 涉及到排序、移動平均等的計算，直接選擇表計算。
- 分解步驟，從交叉表開始，會讓分析過程更加容易理解。
完整的分解過程可以下載：TC2-Common Baseline- Toy Story 公共基準 index.twbx
我的public頁面：tc2 公共基準
3、隨時間變化的銷售總額百分比（多遍聚合）
有時候，我們希望一次執行兩遍表計算，這在會員分析時就很常見，比如在"15大詳細級別表達式"的最後一個案例中，我們想要計算"至少消費1次、2次、N次的顧客的佔比"，分解這句話，"至少"和"佔比"分別對應了"匯總表計算running_sum（）"和"總額百分比" percent of total兩種表計算，在具體實現上，就是兩次表計算的疊加。
在這個case中，「查看某個細分市場隨時間變化的增長或收縮對公司的重要性可能很有意義」，基於時間對每個細分市場做匯總表計算running-sum，確保每個月的銷售額都是年初至今的累計（假想領導查看全年計劃和達成時），然後查看在每個季度或月份中細分市場的總額佔比，即是總額百分比計算。
核心步驟見下圖，
完整版本的步驟，可以下載我的演示檔：TC3 -Percent of total sales over time （Multi-pass aggregation）-2隨時間變化的銷售總額百分比（多遍聚合）.twbx
4、整理時保持有序——凹凸圖
這個題目英文原名叫做 bump chart：trace rank over time，也就是隨著時間跟蹤排序。 本題中，"了解產品在一個月和一年內的排名，然後顯示排名隨時間的變化"，做好的效果是bump chart，也就是凹凸圖。 直接先看一下效果圖，兩側是前後時間的排名，中間是隨年份在各個月份的變化。
1、了解產品在每個年份每個月（年/月）的排名變化
這個問題涉及到兩個字段：日期和銷售。 日期用來生成座標軸，銷售生成排序。 因此，我們先做一個包含這兩個字段的線圖，為了簡化圖形，我們可以先用季度來作圖（下圖上面部分）。
既然要排序，最方便的方式就是右鍵——快速表計算——排序了，實際上就是rank（sum[sales]），左側座標軸會更改為排序的數量（上圖下方）。 注意rank軸超過了12，因此默認計算依據是"表橫穿"，即每個分類從第一個季度到最後一個季度的排序，我們希望改為每個季度不同產品的排序，因此計算依據改為"分類"，即為下圖。
第二步， 兩側添加分類名稱
為了更加方便查看，兩側需要加上開始日期和結束日期的分類標題。 製作兩個時間為列，分類為行的檢視。 最方便的是複製上面的圖，然後把日期加入篩選器篩選，這樣獲得的兩列和上面的圖形加入到一個儀錶板中。
打開高亮顯示，選擇一個分類，確認兩側的位置是否正確，如果不正確，就要回到工作簿進一步排序。 最後刪除兩側的文本部分（移除標籤字段，然後把文本部分摺疊），就會是最開始的圖形。
總結：此圖形的關鍵是理解邏輯。 既然是排序，推薦直接用排序rank，此前官方的說明中使用的rank函數其實是使用了index，雖然結果一樣，但是闡述意思不同，並不推薦這樣使用——除非高手。 index不需要參數，有時會更加方便。
下載我的源檔：TC4-Bump-Chart凹凸圖 喜樂君 .twbx
5.迴圈引用——匯總
這個題目的主題似乎應該是員警局或者是法院，分析的是案件的開案、結案、新開案等的數量。 官方的中文翻譯有點似是而非，看了很久沒有看明白。 完整的說一下：
假設這是一家警局的案件統計，每天有新接到的立案（new cases）、之前接案繼續審查的案件（reopen cases）、當日結案的案件（close cases），我們要做一個可視化視圖，展示昨日累計需要審查的案件數量。
每天的凈案件數量，邏輯自然是：新開案件+重新案件-已結案件。
當日淨案件= SUM（[New Cases]） +SUM（[ReOpened]） - SUM（[Closed]）
而如果要看每天累計的案件數量，就需要返回上面每個欄位的累計數量，從開始到當前日期，我們使用window_sum函數來指定開始和結束的位置。
WINDOW_SUM(expression, [start, end])
返回視窗中表達式的總計。 視窗用與當前行的偏移定義。 使用 FIRST（）+n 和 LAST（）-n 表示與分區中第一行或最後一行的偏移。 如果省略了開頭和結尾，則使用整個分區。
例如，下面的視圖顯示每季度銷售額。 Date 分區中計算的視窗總計返回所有季度的銷售額總計。
這裡用windows_sum函數做匯總，我們可以直接使用上面的「淨增加」新字段，
累計-當日净增加= WINDOW_SUM（[當日净增加]，FIRST（），0）
官方7.0的指南中 用" -index（）"代替了上面的 first，其實不如first更好理解。
而如果我們要在每天看看截止到昨天的累計數量，有兩種方法，一種是在上面的基礎上查找前一個數值，使用Lookup函數 ，如下：
LOOKUP（[累計未結案first]，-1）
而這個結果，和window_sum控制偏移是一樣的，
WINDOW_SUM（[當日净增加]，FIRST（）， -1 ）
最後保留我們需要的一列，改為折線圖即可。
- 下載我的帶說明的檔案： TC5-Running-Total 匯總 .twbx
6、加權平均——計算窗口合計
在這個題目中，每筆訂單都設定了「緊急、高、中、低、無」不同的訂單優先順序（order Priority），我們需要在子分類（sub-category）的詳細級別，根據訂單的優先順序和每個子分類的訂單數量（Order Quantity）計算加權平均——訂單優先順序*數量權重。
在官方的操作中，首先新建了一個計算欄位 Weight
而加權后的優先順序（Weighted Avg Priority）則等於上面的權重，乘以子分類訂單的平均優先順序：
avg([Priority Score])*[Weight]
而「訂單優先順序」（[Priority Score]）則使用了case函數，把四種訂單優先順序文本轉化為了數位，
CASE [Order Priority]
WHEN "Critical" THEN 4
WHEN "High" THEN 3
WHEN "Medium" THEN 2
WHEN "Low" THEN 1
ELSE 0
END
本題目新解：
這個題目官方的方式略有複雜，使用了window-sum函數，可能與早期的版本有關;其實有很多更加簡單的方法。
方法1：手動直接輸入total（）代替window-sum表計算函數，也不需要明示first和last的參數，就可以直接返回整個表的總和;
方法2：在訂單數量的基礎上，使用快速表計算——總額百分比，返回結果就是每個子分類的權重Weight，實際上，快速表計算就是使用了total表計算函數，按兩下膠囊即可發現：
SUM([Order Quantity]) / TOTAL(SUM([Order Quantity]))
7、按計算分組——計算視窗的平均值
本題目比較 每個分類的裝運成本與全部分類的平均裝運成本，按照結果分類兩類：小於平均值、大於等於平均值。 此題目指導我們使用 window-avg表計算函數。
在tableau 9.0版本，引入了Ad-Hoc（臨時計算），我們可以使用臨時計算加速我們的表計算過程，而不需要先寫成計算字段再來驗證欄位的準確性。
在這裡，我們可以在視圖基礎上通過臨時計算輸入 window-avg，然後用兩個字段相減，結果複製到顏色，再進行編輯，就可以生成分類字段，可視化和欄位一氣呵成。
https://www.zhihu.com/video/1100681631106543616
8、移動範圍的事件數——使用window-sum表計算返回指定範圍聚合
這個題目相對而言比較燒腦，涉及到多次參數、表計算、if函數等內容的組合。
本題要展示在一個時間視窗中發生某件事情的次數，我們先看銷售，完整的闡述題目可以如此解釋：在過去的一段時間里，我們想看一下連續一段時間（比如7天）銷售額低於一定數額（比如500）以下的天數超過設置的警戒數量（比如4次）。
首先，我們需要建立幾個參數，把動態值傳遞給視圖，因此可以建立 銷售閥值、週期天數、警戒數量三個參數。
其實，我們要根據銷售額是否低於銷售閥值參數，給每天數據打標籤，因此用到if函數;
如果當天的銷售額低於設定的警戒數量，則標記為1，即發生一次。 我們把這個欄位加入視圖，可以用圓圈代表一次報警事件。
再次，既然要看一個週期的發生次數，就需要使用窗口計算函數window-sum，通過參數指定窗口計算開始的日期和結束的日期，從而計算過去一段時間發生的數量。
根據我們window_sum（expression，first（），last（））的函數，如果我們要看從今天及之前共計7天的數據，應寫作window_sum（expression，-6 ， 0 ） ，這樣包含今天共計7天。 在這裡，我們使用了參數，因此要計算今天及之前共計「參數天」的合計數量，函數應該是：
WINDOW_SUM（ [SalesWarning銷售報警] ， -[Moving Window to Consider時間段]+1 ，0）
最後，我們需要根據指定的警戒次數，來對上面的窗口計算結果做二次的篩選。 既然有臨時計算，我們可以直接在顏色上練習，然後在自定義欄位中詳細說明，比如如下：
之後就是把預警欄位和每天的報警數量通過條形圖和雙軸聯繫在一起。
9、移動平均——指定視窗範圍的移動平均值
如何把移動平均和參數結合起來？ 快速表計算中的移動平均不能輸入參數，因此使用window_avg來代替。 快速表計算中的移動平均，其實就是window_avg的常量版本。
在這裡，如果我們看銷售的過去一段時間的移動平均，考慮到今天的存在，如果要看7天（含今天），first（）的位置改為-6，當前位置為0即可;如果要看過去7天，first參數就改為-7。 因此函數如下：
WINDOW_AVG(SUM([Sales]), -[Periods to Average:]+1, 0)
在把參數加入window_avg函數后，再選擇第二個座標軸改為 雙軸即可。
10、各時段與平均值的差異——使用視窗函數求平均值
有時候，我們想要計算銷售額和季度或者年度的銷售額的差異，這裡用到了上面經常提高的window-avg函數，用來返回一定週期的平均值，然後用每個月的數值和視窗平均值相減。
既然有臨時計算，我們可以直接在視圖中上手輸入window_avg（SUM（[Sales]）），把計算依據改為分區（每年返回一個數值），之後就可以用臨時計算求二者差異。 確定無誤后再創建計算欄位即可。
而同時要計算每個月與當年平均值的差異、與全年平均值的差異，我們需要兩次窗口計算，修改計算依據即可。
Apr 13, 2019 update
Apr 14, 2019