Create level of detail and rank calculations

You can use calculated fields to create new data using data that already exists in your data source. Tableau Prep Builder supports many of the same calculation types as Tableau Desktop. For general information about creating calculations, see Get Started with Calculations in Tableau(Link opens in a new window).

Note: Some functions supported in Tableau Desktop may not yet be supported in Tableau Prep Builder. To view the available functions for Tableau Prep Builder, review the function list in the Calculation editor.

Starting in version 2020.1.3 Tableau Prep Builder you can use FIXED Level of Detail (LOD) and RANK and ROW_NUMBER analytic functions to perform more complex calculations.

For example, add a FIXED LOD calculation to change the granularity of fields in your table, use the new ROW_NUMBER () analytic function to quickly find duplicate rows, or use one of the new RANK () functions to find the top N or bottom N values for a selection of rows with similar data. If you want a more guided experience when building these types of expressions, you can use the new visual calculation editor.

Calculate level of detail

When you need to calculate data at multiple levels of granularity in the same table, you can write a level of Detail (LOD) expression to do this. For example, if you wanted to find the total sales for each region, you could write a calculation like {FIXED [Region] : SUM([Sales])}.

Tableau Prep Builder supports the FIXED level of detail expression and uses the syntax {FIXED [Field1],[Field2] : Aggregation([Field)}.

LOD expressions have two parts to the equation that are separated by a colon.

  • FIXED [Field] (required): This is the field or fields that you want to calculate the values for. For example if you wanted to find the total sales for customer and region, you would enter FIXED [Customer ID], [Region]:. If you don't select any fields, this is the equivalent to performing the aggregation defined on the right side of the colon and repeating that value for every row.

  • Aggregation ([Field]) (required): Select what you want to calculate and what level of aggregation you want. For example if you want to find the total sales, then enter SUM([Sales].

When using this feature in Tableau Prep Builder, the following requirements apply:

  • INCLUDE and EXCLUDE LOD expressions aren't supported.
  • Aggregation calculations are only supported inside an LOD expression. For example, SUM([Sales]) would not be valid, but {FIXED [Region] : SUM([Sales])} is valid.
  • Nesting expressions inside an LOD expression isn't supported.  For example, { FIXED [Region] : AVG( [Sales] ) / SUM( [Profit] )} isn't supported.
  • Combining an LOD expression with another expression isn't supported.  For example [Sales]/{ FIXED [Country / Region]:SUM([Sales])} isn't supported.
  • Create Level of Detail (LOD) calculations

    To create a level of detail calculation, 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 Builder writes the calculation for you.

    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 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 Builder 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 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.
    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 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 Builder 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 Builder 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] DESC: RANK() }}

When using this feature in Tableau Prep Builder, the following requirements apply:

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

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).
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 Builder 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 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 ROW_NUMBER function to add a row number to the field Row ID using the expression {PARTITION [Row ID]: {ORDERBY[Row ID]:ROW_NUMBER()}} and click Save.

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

    BeforeAfter

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 who's rows you want to compute values for. This creates the Partition part of the 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 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.

 

Thanks for your feedback! There was an error submitting your feedback. Please try again.