原文地址：5 Tableau Table Calculation Functions That You Need to Know
September 29, 2014 by Tom McCullough
从一个开发者角度看，Tableau优雅而又最让人惊喜的是高效而容易的处理、计算数据的能力。
计算字段可以让我们对比字段、计算聚合、逻辑判断、合并字符串、转化数据格式，还有非常多的分析和数学算数公式，而这些都不需要改变数据库的数据。计算字段返回一个可用的、随时拖拽的字段到数据窗口，和数据缓存表中，你甚至可以在计算之后再执行二次计算。太神奇了！
为了让计算更加强大，Tableau可以让在完成数据提取之后，增加新的计算。表计算就是用来处理缓存表中的数据，然后在视图返回结果。
这是什么意思呢？一旦生成数据视图，我们可以让Tableau展现类似于数值、条形图、散点图之恋的结果，并在这些基础在计算。表计算的重要之处在于，不想计算字段帮我们对比两个或多个度量，它可以让我们对比单个数值本身。
下面是五个常见的Tableau表计算函数。
In my developer mind, what takes Tableau 8 from neat to amazing is the ability to manipulate, calculate and maneuver data quickly and easily.
Calculated fields allow you to compare fields, apply aggregations, apply logic, concatenate strings, convert dates or perform a myriad of other analytical and mathematical functions on your data without needing to make changes to your database at all. Calculated fields return a re-usable, drag-and-drop field into your Data window and the cache table. You can even calculate on top of your calculations. Amazing!
To take this functionality even further, Tableau allows you to do additional calculations on your data after Tableau has completed a query of the database. Table Calculations address data in the cache table and allow you to perform calculations on visible results.
So, what does that mean?
Once my visualization is rendered, I can ask Tableau to literally “look” at the results (numbers, bars, plots) and then do math on those visible results. Most importantly, unlike calculated fields that allow you to compare two or more separate measures in your data set, table calculations allow you to compare a singular measure to itself.
Here are five Tableau table calculation functions that will help me explain:
LOOUP（）函数
LOOUP函数是做最喜欢的表计算，考察快速表计算中的差异和差异百分比，我们可以发现它的用法。
LOOKUP()
LOOKUP() is my favorite table calculation. This one very literally allows you to “look” at different results in your visualization. Using the context of the quick table calculations, Difference and Percent Difference, we can see how LOOKUP() is being used and how it works.
Difference 差异
fig. 1 – Deconstructed view of a Difference quick table calculation (running Table Across).“差异”的分解视图
通过差异，我们想对比一个数值和它前面分区的数值。注意第二个数值是如何被引用的(包含在lookup函数之中)，首先返回前一个分区的数值，然后减去后面的这个数值，以此类推。
-1指引的是以当前分区为基础，查找返回哪一个分区的数值。可以修改为-1，1或者2看一下结果。(PS这句话翻译的有点不好)
In the Difference table calculation, we want to compare a value against itself in a previous (or simply, a different) partition. Notice how the second value in the calculation is wrapped in a LOOKUP() function, which “looks” at the previous partition’s value and then subtracts it from the next one, etc. The -1 declares which partition is in relation to the base of the calculation. Change the -1 to 1 or -2 to see the change in the result.
Percent Difference
fig. 2 – Deconstructed view of a Percent Difference quick table calculation (running Table Across). “差异百分比”的分解视图
在差异百分比中，lookup 的用法和“差异”一样，差别只在于方程式中增加的额外数值——把前面的数值作为分母。然而逻辑是一样的。参数-1告诉Tableau返回前面的数值。
In Percent Difference, the LOOKUP() function is used in the same manner as in Difference. This one differs in the extra value in the equation, adding the denominator as the previous value. The logic is the same, however. The -1 argument tells Tableau to “look” at the previous partitions’ values.
Why I use LOOKUP() in every workbook:
我为什么在每个工作簿中都是用lookup函数？ ？？？
它对于筛选机制非常有用。通过应用下面的表计算，你可以应用维度筛选器，而不会影响视图中的表计算。(PS表计算的优先级低于维度筛选)
LOOKUP(MIN([Your_Dimension]), 0)
LOOKUP() is extremely helpful as a filtering mechanism. By using the following table calculation, you can filter using a dimension without wrecking any other table calculations in your viz:
LOOKUP(MIN([Your_Dimension]), 0)
这个表计算要查找每一行数据，然后用min函数标记数值。看上去没有意义，然后却非常重要，功能消极而强大。(PS作者说这个功能passive，是说虽然没有直接引起视图改变 但是可以防止出错——此为消极被动)
The table calculation “looks” at each row and labels it with its MIN value. Seems like nonsense, but this is incredibly, though passively, powerful. More on this in a future post.
TOTAL() 求和
Total()，虽然是基于数据库的计算，但却属于一种表计算，就像window_sum()函数一样，它，依据指定分区和方向，把视图中所有结果累加起来。一个简单的应用是快速计算中的总额百分比。
TOTAL(), though still acting upon a database calculation, is a table calculation that, like WINDOW_SUM(), simply adds all visible results along the direction and scope assigned. An easy example of TOTAL() can be found in the quick table calculation, Percent of Total.
fig. 3 – Deconstructed view of a Percent of Total quick table calculation (running Table Across).快速表计算总额百分比的分解视图
这是非常直接而有效的解释Tableau如何应用的方法。
This is very straightforward, though still helpful in explaining how Tableau table calculations work.
For a great explanation on the differences between TOTAL() and WINDOW_SUM(), check out this link: http://community.tableausoftware.com/docs/DOC-5640
ROW COUNTERS 行计数
我在每一个工作表中都使用行计数的表计算，我这里有很多的原因。
I include the row counter table calculation functions in every workbook, and I do it for many reasons.
INDEX() or RANK()
这两个函数为每一行或者依据表计算指定的区域和范围为每个分区，提供了增量序号。一般来说这个函数为每一行或者是分区返回一个排序数值。rank函数还有很多其他的功能，(?)
These two functions provide a unique incremental number for every row or partition assigned in the scope and direction of the table calculation. Basically, this table calculation will give you a rank field for every row or pane you can see in your viz. The RANK() function has additional functionality allowing for addressing ties and other groupings based on rank.
FIRST()
First() 第一行返回数值0，之后行或分区负数递增。
last()刚好相反，最后的一行返回数值0，之后的正数递增。
This function returns a 0 for the first visible row and then negative incremental numbers for the following rows or partitions.
LAST()
This function, obviously the opposite of FIRST(), returns a 0 for the last visible row and then positive incremental numbers for the subsequent rows.
fig. 4 – Deconstructed view of INDEX(), FIRST(), and LAST() table calculation functions (running Table Down), sorted Descending by Sales.
为什么这么简单的函数却有如此强大的功能， 因为当试图中还有其他表计算的时候 这些行级别的函数在筛选器中会非常有用。
What makes these very simply functions so powerful? The row-counting functions can be leveraged in filters when your viz includes other table calculations.
Conclusion总结
在Tableau的仓库里面有很多的表计算和公式。通过构建和查看这五个表计算应用，我们可以理解背后的原理，从而更好的使用Tableau的高级功能。
记住，要比较数值和它自己，只有一个办法就是表计算。千万不要犹豫去理解它的功能，你会比你想象的更喜欢它。
There are many, many more table calculations and functions within the powerhouse of Tableau. However, by deconstructing and seeing how these five table calculation functions actually work, I now understand the machinery behind the scenes and can more quickly utilize more advanced features in my Tableau vizzes.
Remember, the only way to compare a measure against itself is to use table calculations. Don’t hesitate to understand this powerful feature in Tableau. You’ll use it more often than you think.
喜乐君 