创建详细级别、排名和磁贴计算

可以使用计算字段，使用数据源中已存在的数据创建新数据。Tableau Prep 支持许多与 Tableau Desktop 相同的计算类型。有关创建计算的一般信息，请参阅开始使用 Tableau 中的计算（链接在新窗口中打开）.

从版本 2020.1.3 Tableau Prep Builder 和 Web 开始，您可以使用固定详细级别 （LOD） 和 RANK 以及ROW_NUMBER分析函数来执行更复杂的计算。

例如，添加 FIXED LOD 计算以更改表中字段的粒度，使用新的 ROW_NUMBER （） 分析函数快速查找重复行，或使用新的 RANK （） 函数之一查找具有相似数据的所选行的前 N 个或后 N 个值。如果您希望在生成这些类型的表达式时获得更直观的体验，则可以使用新的可视化计算编辑器。

在版本 2021.4.1Tableau Prep Builder 和 Web 上，您可以使用磁贴功能将行分发到指定数量的存储桶中。

注意：Tableau Desktop 中支持的某些功能可能尚不受 Tableau Prep 支持。要查看 Tableau Prep 的可用函数，请查看计算编辑器中的函数列表。

计算详细程度

如果需要在同一表中以多个粒度级别计算数据，则可以编写详细级别 （LOD） 表达式来执行此操作。例如，如果要查找每个区域的总销售额，可以编写类似 的计算。{FIXED [Region] : SUM([Sales])}

Tableau Prep 支持 FIXED 细节级别表达式，并使用语法 。{FIXED [Field1],[Field2] : Aggregation([Field)}

LOD 表达式在等式中有两个部分，由冒号分隔。

• FIXED [字段]（必填）：这是要计算其值的一个或多个字段。例如，如果要查找客户和区域的总销售额，请输入 。如果未选择任何字段，则等效于执行在冒号右侧定义的聚合，并为每行重复该值。FIXED [Customer ID], [Region]:

• 聚合（[字段]）（必需）：选择要计算的内容以及所需的聚合级别。例如，如果要查找总销售额，请输入 。SUM([Sales]

在 Tableau Prep 中使用此功能时，以下要求适用：

• INCLUDE和 LOD 表达式不受支持。EXCLUDE

• 聚合计算仅在 LOD 表达式中受支持。例如，将无效，但有效。SUM([Sales]){FIXED [Region] : SUM([Sales])}

• 不支持在 LOD 表达式中嵌套表达式。例如，不受支持。{ FIXED [Region] : AVG( [Sales] ) / SUM( [Profit] )}

• 不支持将一个 LOD 表达式与另一个表达式组合。例如，不受支持。[Sales]/{ FIXED [Country / Region]:SUM([Sales])}

创建详细级别 （LOD） 计算

若要创建详细级别计算，您可以使用计算编辑器自行编写计算，或者，如果您想要更直观的体验，则可以使用可视化计算编辑器，您可以在其中选择字段，Tableau Prep 将为您编写计算。

计算编辑器

1. 在“配置”窗格工具栏中，单击“创建计算字段”，或者在纵断面卡或数据网格中，单击“更多选项”菜单，然后选择“创建计算字段”>自定义计算”。

   

2. In the Calculation editor, enter a name for your calculation and enter the expression.

   For example, to find the average days to ship products by city, create a calculation like the one shown below.

   

Visual Calculation editor

Select fields from a list and Tableau Prep builds the calculation for you as you make your selections. A preview of the results is shown in the left pane so you can see the results of your selections as you go.

1. In a profile card or results pane, click the More options menu and select Create Calculated Field >Fixed LOD.

   

2. In the Visual Calculation editor, do the following:

• In the Group by section, select the fields that you want to calculate the values for. The field where you selected the Create Calculated Field >Fixed LOD menu option is added by default. Click the plus icon to add any additional fields to your calculation. This populates the left side of the equation, .{FIXED [Field1],[Field2] :

• In the Compute using section, select the field that you want to use to calculate your new values. Then select your aggregation. This populates the right side of the equation, .Aggregation([Field)}

  A graphic below the field shows the distribution of values and a total count for each value combination. Depending on the type of data, this can be a box plot, range of values, or the actual values.

  

  Note: Available aggregation values vary by the data type assigned to the field.

• To remove a field, right-click or Cntrl-click (MacOS) in the drop-down box for the fields in the Group by section and select Remove Field.

• In the left pane, double-click in the field header and enter a name for your calculation.

3. Click Done to add your new calculated field. In the Changes pane, you can see the calculation that Tableau Prep generated. Click Edit to open the visual calculation editor to make any changes.

   

Calculate rank or row number

Analytic functions, sometimes referred to as window calculations, enable you to perform calculations across the entire table, or a selection of rows (partition) in your data set. For example, when applying a rank to a selection of rows, you would use the following calculation syntax:

{PARTITION [field]: {ORDERBY [field]: RANK() }}

• PARTITION (optional): Designate the rows you want to perform the calculation on. You can specify more than one field, but if you want to use the entire table, omit this part of the function and Tableau Prep treats all the rows as the partition. For example .{ORDERBY [Sales] : RANK() }

• ORDERBY (required): Specify one or more fields that you want to use to generate the sequence for the rank.

• Rank () (required): Specify the rank type or ROW_NUMBER () you want to calculate. Tableau Prep supports RANK(), RANK_DENSE(), RANK_MODIFIED(), RANK_PERCENTILE(), and ROW_NUMBER() functions.

• DESC or ASC (optional): Represents descending (DESC) or ascending (ASC) order. By default, rank is sorted in descending order, so you don't need to specify this in the expression. If you want to change the sort order, add ASC to the expression.

  You can also include both options in the function. For example if you wanted to rank a selection of rows, but wanted to sort the rows in ascending order, then apply the rank in descending order, you would include these two options in the expression. For example: {PARTITION [Country], [State]: {ORDERBY [Sales] ASC,[Customer Name] DESC: RANK() }}

When using this feature, the following requirements apply:

• Nesting expressions inside a RANK () function isn't supported. For example, isn't supported.[Sales]/{PARTITION [Country]: {ORDERBY [Sales]: RANK() }} / SUM( [Profit] )}

• Combining a RANK () function with another expression isn't supported. For example isn't supported.[Sales]/{PARTITION [Country]: {ORDERBY [Sales]: RANK() }}

Supported analytic functions

Function	Description	Example
RANK ()	Assigns a whole number rank starting with 1, in ascending or descending order to each row. If rows have the same value, they share the rank that is assigned to the first instance of the value. The number of rows with the same rank is added when calculating the rank for the next row, so you may not get consecutive rank values.
RANK_DENSE()	Assigns a whole number rank starting with 1 in ascending or descending order to each row. If rows have the same value, they share the rank that is assigned to the first instance of the value, but no rank values are skipped so you will see consecutive rank values.
RANK_MODIFIED()	Assigns a whole number rank starting with 1, in ascending or descending order to each row. If rows have the same value, they share the rank that is assigned to the last instance of the value. No rank values are skipped. Rank_Modified is calculated as .Rank + (Rank + Number of duplicate rows - 1)
RANK_PERCENTILE()	Assigns a percentile rank from 0 to 1 in ascending or descending order to each row. RANK_PERCENTILE is calculated as .(Rank-1)/(Total rows-1) Note: In the event of a tie, Tableau Prep rounds the rank down, similar to PERCENT_RANK() in SQL.
ROW_NUMBER()	Assigns a sequential row ID to each unique row. If you have duplicate rows and use this calculation, your results might change each time you run the flow if the order of rows changes.

Create Rank or Row Number calculations

To create a Rank or Row_Number calculations, you can use the Calculation editor to write the calculation yourself or if you want a more guided experience, you can use the Visual Calculation editor where you select your fields and Tableau Prep writes the calculation for you.

Note: ROW_NUMBER () calculations aren't available in the visual calculation editor.

Calculation editor

Use the Calculation editor to create any of the supported RANK () or ROW_NUMBER() calculations. The list of supported analytic calculations is shown in the Calculation editor in the Reference drop-down under Analytic.

1. In the Profile pane toolbar click Create Calculated Field, or in a profile card or data grid, click the More options menu and select Create Calculated Field > Custom Calculation.

   

2. In the Calculation editor, enter a name for your calculation and enter the expression.

   For example to find the latest customer order, create a calculation like the one shown below, then keep only the customer order rows that are ranked with the number 1.

   

Example: Use ROW_NUMBER to find and remove duplicate values.

This example uses the Superstore sample data set in Tableau Prep Builder to find and remove exact duplicate values for the field Row ID using the ROW_NUMBER function.

1. Open the Sample Superstore flow.

2. In the Flow pane, for the Input step Orders West, click on the Clean step Rename States.

3. In the toolbar, click Create Calculated Field.

4. In the Calculation editor, use the function to add a row number to the field Row ID using the expression and click Save.ROW_NUMBER{PARTITION [Row ID]: {ORDERBY[Row ID]:ROW_NUMBER()}}

   

5. In the new calculated field, right-click or Cmd-click (MacOS) on the field value 1, then select Keep Only from the menu.

   

   Before	After

Visual Calculation editor

Just like when creating a level of detail calculation, you can use the visual calculation editor to build a rank calculation. Select the fields you want to include in the calculation, then select the fields you want to use to rank the rows and the type of rank you want to calculate. A preview of the results is shown in the left pane so you can see the results of your selections as you go.

1. In a profile card or results pane, click the More options menu and select Create Calculated Field >Rank.

   

2. In the Visual calculation editor, do the following:

• In the Group by section, select the fields with rows you want to compute values for. This creates the Partition part of the calculation.

  After you select your first field, click the plus icon to add any additional fields to your calculation. If you want to include all rows or remove a selected field, right-click or Cmd-click (MacOS) in the drop-down box for the fields in the Group by section and select Remove Field.

• In the Order by section, select the fields that you want to use to rank your new values. The field where you selected the Create Calculated Field >Rank menu option is added by default.

  Click the plus icon to add any additional fields to your calculation, then select your Rank type. Click the sort icon to change the rank order from descending (DESC) to ascending (ASC).

  Note: Rank values vary by the data type assigned to the field.

• In the left pane, double-click in the field header and enter a name for your calculation.

  

3. Click Done to add your new calculated field. In the Changes pane, you can see the calculation that Tableau Prep Builder generated. Click Edit to open the visual calculation editor to make any changes.

   

Calculate tiles

Use the Tile feature to distribute rows into a specified number of buckets by creating a calculated field. You select the fields that you want to distribute by, and the number of groups (tiles) to be used. You can also select additional fields for creating partitions where the tiled rows are distributed into groups. Use the Calculation editor to input the syntax manually or use the Visual Calculation editor to select the fields and Tableau Prep writes the calculation for you.

For example, if you have rows of student data and wanted to see which students are in the top 50% and bottom 50%, you can group the data into two tiles.

The following example shows two groups for the upper and lower half of student grades. The syntax for this method is:

{ORDERBY [Grade] DESC:NTILE(2)}

You can also create a partition, where each value of a field is a separate partition, and divide data into groups for each partition.

The following example shows creating partitions for the Subject field. A partition is created for each subject and two groups (tiles) are created for the Grade field. The rows are then distributed evenly into the two groups for the three partitions. The syntax for this method is:

{PARTITION [Subject]:{ORDERBY [Grade] DESC:NTILE(2)}}

Create Tile calculations

To create tile calculations, you can use the Calculation editor to write the calculation yourself or if you want a more guided experience, you can use the Visual Calculation editor where you select your fields and Tableau Prep writes the calculation for you.

Visual Calculation editor

When you use the visual calculation editor to create a tile calculation, a preview of the results is shown in the left pane.

1. Select a profile card to create a tile calculation.

2. Click the More options menu and select Create Calculated Field > Tile.

   

   The selected profile card is added as an ORDERBY field.

3. In the Visual calculation editor, do the following:

• The default value for Tiles is 1. Increase the number of tiles to add more groupings.

• In the Group by section, select the fields of the rows you want to compute values for. This creates the Partition part of the calculation. You can have multiple Group by fields for a single calculation.

  Click the plus icon to add any additional fields to your calculation. If you want to include all rows or remove a selected field, right-click or Cmd-click (MacOS) in the drop-down box for the fields in the Group by section and select Remove Field.

• In the left pane, double-click in the field header and enter a name for your calculation.

• In the Order by section, select one or more fields that you want to use to group and distribute your new values. You must have at least one Order by field. The field where you selected the Create Calculated Field >Tile menu option is added by default.

4. To sort the results, do the following:

• Click any of the Calculation rows to filter the results for the selected grouping

• Change the ascending or descending order of the order by field.

5. Click Doneto add your new calculated field.

6. In the Changes pane, you can see the calculation that Tableau Prep Builder generated. Click Edit to open the visual calculation editor to make any changes.

   The following example shows a quartile division of rows. A partition is created based on four US regions and then the Sales field data is evenly grouped into the partitions.

   

Calculation editor

1. In the Profile pane toolbar, click Create Calculated Field, or in a profile card or data grid, click the More options menu and select Create Calculated Field > Custom Calculation.

   

2. In the Calculation editor, enter a name for your calculation and enter the expression. For example, to order rows of students by grades into two groups and then group them by subject, use : .{PARTITION [Subject]:{ORDERBY [Grade] DESC:NTILE(2)}}

   

   磁贴计算包括以下元素：

• PARTITION（可选）：分区子句将结果集的行不同到使用 NTILE（） 函数的分区中。

• ORDERBY（必需）ORDER BY 子句定义使用 NTILE（） 的每个分区中行的分布。

• NTILE（必需）：NTILE 是将行划分为的整数。

  注： 当所有行都可以被 NTILE 子句整除时，该功能会在切片数中平均分配行。当行数不能被 NTILE 子句整除时，生成的组将划分为不同大小的条柱。

• DESC 或 ASC（可选）：表示降序 （DESC） 或升序 （ASC）。默认情况下，磁贴按降序排序，因此无需在表达式中指定此项。如果要更改排序顺序，请将 ASC 添加到表达式中。

3. 点击保存。

   生成的字段显示与表中每一行关联的切片分组（条柱）分配。