【Tableau】10大Tableau表计算——深度解读

【202007】在最新出版的图书《数据可视化分析：Tableau原理与实践》中，详细介绍了计算的原理、选择与使用情形，把表计算优先于LOD表达式讲解，并总结了几个使用的关键要求，引用了本章节的部分题目。

京东：《数据可视化分析：Tableau原理与实践》

之前写lod博客，特别是完整的分析了官方的“15大详细级别表达式案例”；在整理表计算的过程中，我想把表计算的10大案例，也用中文的方式完整复原，更接近于初学者的理解，同时穿插讲解表计算主要表达式的语法和用法。

【最新】近期更新了10大表计算的完整视频版本，重新录制并提供了最新版本的文件下载，需要可以点击： LOD高级分析视频课程

本视频课程主要诠释Tableau高级分析之 LOD表达式和TC表计算。

• 英文官方链接：Top 10 Tableau table calculations2015/01/15 by ELLIE FIELDS
• 中文官方链接：10 大 Tableau 表计算  2015/01/15 by ELLIE FIELDS

本文的必要性，在于软件本身的与时俱进：

1、官方在提供10大表计算case时，还不支持临时计算（Tableau 9+），因此官方指南都是先做计算字段，再展现图形；有了临时计算，推荐大家先写临时计算，反复修改、证伪，再去转化为固定的自定义计算字段保存；

2、早期的部分表计算有了更简单的表计算函数代替，比如window_sum(expression, first(), last()) 用来计算整个窗口聚合，如今可以省略后面的两个参数，也可以直接用total()代替；旧版本中使用 -index() 定位和当前位置偏移的时候，也可以直接使用first()，更容易理解；（TC5）

3、部分手动的表计算，可以使用快速表计算代替，比如TC6中计算权重的步骤，可以用快速表计算-总额百分比加速分析过程；

TC5中使用window-sum，其实可以直接使用running-total完成，更快更方便。

4、某些功能有了更加方便的用法，比如第一个案例中，必须使用参数来手动输入日期，但是使用2019.2最新的parameter action功能，可以直接从视图中提取参数，而无需选择，参考下文的案例【Tableau】参数动作：高级互动的新秀之作）

为了帮助大家学习，我的每个案例解说结尾，大家可以下载我的详细步骤文件，以及public的分享链接。

各大案例难度系数如下：

表计算基础知识

我的完整的参考文件，可以从这里下载：TC1-Relative-Stock-Return 股票相对变化.twbx 

或者参考我的Tableau Public主页内容链接：Public -股票相对变化。

//Jun 19, 2019 update

在新版本desktop中，Tableau推出了参数动作，也就是无需通过右侧手动选择参数，而是可以直接从视图中中拾取，比如点击视图中的一个点，对应的日期自动转化为参数，传递给视图。通过这个功能，可以进一步优化参数的体验。具体参考参数动作：【Tableau】参数动作：高级互动的新秀之作

2、公共基准（《玩具总动员》）（index索引表计算）

3、随时间变化的销售总额百分比（多遍聚合）

完整版本的步骤，可以下载我的演示文件：TC3 -Percent of total sales over time (Multi-pass aggregation)-2随时间变化的销售总额百分比（多遍聚合）.twbx

或者参考我的public主页：多遍聚合

4、整理时保持有序——凹凸图

这个题目英文原名叫做 bump chart：trace rank over time，也就是随着时间跟踪排序。本题中，“了解产品在一个月和一年内的排名，然后显示排名随时间的变化”，做好的效果是bump chart，也就是凹凸图。直接先看一下效果图，两侧是前后时间的排名，中间是随年份在各个月份的变化。

第一步 了解产品在每个年份每个月（年/月）的排名变化

这个问题涉及到两个字段：日期和销售。日期用来生成坐标轴，销售生成排序。因此，我们先做一个包含这两个字段的线图，为了简化图形，我们可以先用季度来作图（下图上面部分）。

既然要排序，最方便的方式就是快速表计算——排序了，实际上就是rank(sum[sales])，左侧坐标轴会更改为排序（上图下方）。注意rank轴超过了12，因为默认计算依据是“表横穿”，即每个分类从第一个季度到最后一个季度的排序，我们希望改为每个季度不同产品的排序，因此计算依据改为“分类”，即为下图：

第二步  两侧添加分类名称

为了更加方便查看，两侧需要加上开始日期和结束日期的分类标题。制作两个时间为列，分类为行的视图。最方便的是复制上面的图，然后把日期加入筛选器筛选，这样获得的两列和上面的图形加入到一个仪表板中。

打开高亮显示，选择一个分类，确认两侧的位置是否正确，如果不正确，就要回到工作簿进一步排序。最后删除两侧的文本部分（移除标签字段，然后把文本部分折叠），就会是最开始的图形。

总结：此图形的关键是理解逻辑。既然是排序，推荐直接用排序rank，官方的说明中使用的rank函数其实是使用了index，虽然结果一样，但是阐述意思不同，并不推荐这样使用——除非高手。index不需要参数，有时会更加方便。

下载我的源文件：TC4-Bump-Chart凹凸图 喜乐君 .twbx

参见我的public 主页：bump chart 

当然，如果能加一个双轴图，可以做成下面的样子。

5.循环引用——汇总

这个题目的主题似乎应该是警察局或者是法院，分析的是案件的开案、结案、新开案等的数量。官方的中文翻译有点似是而非，看了很久没有看明白。完整的说一下：

假设这是一家警局的案件统计，每天有新接到的立案（new cases）、之前结案继续审查的案件（reopen cases）、当日结案的案件（close cases），我们要做一个可视化视图，显示每天还需要审查的案件数量（今天的审查数量，对应到昨天的累计未结案数量）；通过通过工具提示显示今天的新开、重开、结案数量。

每天的净案件数量，逻辑自然是：新开案件+重新案件-已结案件。

当日净案件= SUM([New Cases]) +SUM([ReOpened]) – SUM([Closed])

A、window-sum方法

而如果要看每天累计的案件数量，就需要返回上面每个字段的累计数量，从开始到当前日期，我们使用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控制偏移是一样的，我们推荐用这个方法，能用一次表计算完成的，不要用两个表计算，否则增加设置和出错的概率。这里不建议使用官方说明中的lookup表计算。

WINDOW_SUM([当日净增加],FIRST(), -1 )

B、running-total方法

大家可能注意到，这里的窗口计算，其实就是running-total，因此我们可以直接在[当日净案件]这个字段上，直接使用快速表计算——汇总就可能完成 上面 WINDOW_SUM([当日净增加],FIRST(),0) 的同样的结果。 之后，再用lookup偏移一位查找，会比上面的方法更快。

 最后保留我们需要的一列，改为折线图即可。

• 下载我的带说明的文件： 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，然后用两个字段相减，结果复制到颜色，再进行编辑，就可以生成分类字段，可视化和字段一气呵成。

这个题可以非常好的检验一个人对Tableau即席计算的熟悉程度。

9、移动平均——指定窗口范围的移动平均值

如何把移动平均和参数结合起来？ 快速表计算中的移动平均不能输入参数，因此使用window_avg来代替。快速表计算中的移动平均，其实就是window_avg的常量版本。

在这里，如果我们看销售的过去一段时间的移动平均，考虑到今天的存在，如果要看7天（含今天），first()的位置改为-6，当前位置为0即可；如果要看过去7天，first参数就改为-7。因此函数如下：

WINDOW_AVG(SUM([Sales]),  -[Periods to Average:]+1, 0)

在把参数加入window_avg函数后，再选择第二个坐标轴改为 双轴即可。

8、移动范围的事件数——使用window-sum表计算返回指定范围聚合

//本题目放在TC9之后来练习，使用了TC9中的参数理念。 在最新版本的视频课程中，我分为了：逻辑计算过程、可视化生成过程、可视化增强分析三个步骤，介绍了这个题目的完整方法。

这个题目相对而言比较烧脑，涉及到多次参数、表计算、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)

最后，我们需要根据指定的警戒次数，来对上面的窗口计算结果做二次的筛选。既然有临时计算，我们可以直接在颜色上练习，然后在自定义字段中详细说明，比如如下：

之后就是把预警字段和每天的报警数量通过条形图和双轴联系在一起。

10、各时段与平均值的差异——使用窗口函数求平均值

有时候，我们想要计算销售额和季度或者年度的销售额的差异，这里用到了上面经常提高的window-avg函数，用来返回一定周期的平均值，然后用每个月的数值和窗口平均值相减。

既然有临时计算，我们可以直接在视图中上手输入window_avg(SUM([Sales])),把计算依据改为分区（每年返回一个数值），之后就可以用临时计算求二者差异。确定无误后再创建计算字段即可。

而同时要计算每个月与当年平均值的差异、与全年平均值的差异，我们需要两次窗口计算，修改计算依据即可。

总结

表计算是Tableau高级分析功能的必备工具，结合上面的使用指南，会增加每个人的表计算技术。推荐大家参考我的视频课程，包含了上述十个例子的完整制作过程。

【视频】Tableau广义LOD表达式 

更多表计算：

• 【Tableau】TC- 表计算 (上)_聚合计算与表计算入门
• 【Tableau】TC- 表计算(下)快速表计算

Apr 13, 2019 update
Oct 5, 2019 更新视频