tableau prep builder（tableau官网）

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

英文官方链接：Top 10 Tableau table calculations2015/01/15 by ELLIE FIELDS中文官方链接：https://www.tableau.com/zh-cn/about/blog/top-10-tableau-table-calculations​www.tableau.com/zh-cn/about/blog/top-10-tableau-table-calculations10 大 Tableau 表计算Tableau表计算官方​www.tableau.com/zh-cn/about/blog/top-10-tableau-table-calculations

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

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() 函数，可轻松计算自首映开始的星期数。在这个例子中，您按电影分区，按天数寻址。

总结：

涉及到排序、移动平均等的计算，直接选择表计算。分解步骤，从交叉表开始，会让分析过程更加容易理解。

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

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