Table Calculations Overview

🕐 4 min read

Supported calculations
Table calculation types
Extra customizations per type

A table calculation is a transformation you apply to the values in a query. Table calculations are a special type of calculated field that computes on local data in Tableau. They are calculated based on what is currently in the query.

You can use table calculations for a variety of purposes, including:

Transforming values to rankings
Transforming values to show running totals
Transforming values to show a percent of total

Here is an example of a visualization with no table calculations.

Tableau viz with no table calculation

The equivalent VizQL Data Service (VDS) query with no table calculation applied is:

 "query": {
    "fields": [
        {
            "fieldCaption": "Region"
        },
 	 {
            "fieldCaption": "Order Date",
            "function": "YEAR"
        },{
            "fieldCaption": "Sales",
            "function": "SUM"
        },
        {
            "fieldCaption": "Profit",
            "function": "SUM"
        }
    ]
  }

For any VDS query or in the Tableau user interface, there is a virtual table that is determined by the dimensions in the query. Think of these dimensions as the ones that are “in the view” in the Tableau user interface. In the preceding example, these dimensions are YEAR(Order Date) and Region.

Supported calculations

VDS supports the following calculations:

Basic row level calculations
Aggregate calculations
Table calculations and predictive modeling calculations
Level of detail (LOD) expressions
Logical calculations (if / then / else)
String / date / number / type conversions
Parameter-based calculations
User functions

Note: The best way to ramp up on how to use table calculations in VDS is to compare the OpenAPI Schema with the Quick Table Calculations dialog in Tableau.

Each table calculation requires you to specify a field to create the table calculation from, a tableCalcType, and the dimensions to include. The dimensions you include in your query and the ordering determine the Compute Using, that is, the addressing and partitioning fields.

All table calculations must have the field on which you are operating, the table calculation type, and the list of dimensions (see more below). For each table calculation type, there could be additional requirements or customizations in accordance with the Quick Table calculations dialog.

OpenAPI basics for a table calculation field:

{
  "fieldCaption": "string",
  "tableCalculation": {
    "tableCalcType": "string",
    "dimensions": [ // Same as "Specific Dimensions" listed above      {
        "fieldCaption": "string",
      }
    ]
  }
}

Note: The dimensions field tells VDS which fields to use for partitioning and addressing in the table calculation. In other words, it tells VDS how to organize and group the data for this calculation. The table calculation is performed separately within each partition. For more information, see The basics: addressing and partitioning.

Table calculation types

The VDS OpenAPI schema allows for the following table calculation types. Depending on the type of table calculation, you will be required to provide extra fields as well, which corresponds to the various table calculation types that you would see in the Tableau UI dialog and what you would need to provide for each of those.

    "tableCalcType": {
        "type": "string",
        "enum": [
            "CUSTOM",
            "DIFFERENCE_FROM",
            "PERCENT_DIFFERENCE_FROM",
            "PERCENT_FROM",
            "PERCENT_OF_TOTAL",
            "RANK",
            "PERCENTILE",
            "RUNNING_TOTAL",
            "MOVING_CALCULATION"
        ]

Calculation Type Rank dropdown menu

We will now walk through examples of each type of table calculation.

Extra customizations per type

Different types of table calculations have advanced configuration options. You can see these in the Tableau user interface dialog as well if you’re creating Quick Table calculations.

Rank table calculation options

{
  "rankType": "COMPETITION|MODIFIED COMPETITION|DENSE|UNIQUE",
  "direction": "ASC|DESC"
}

COMPETITION: Standard ranking (1, 2, 3, 4…)
MODIFIED COMPETITION: Modified ranking (1, 2, 3, 3, 5…)
DENSE: Dense ranking (1, 2, 3, 3, 4…)
UNIQUE: Unique ranking (1, 2, 3, 4, 5…)

Difference table calculation options

{
  "relativeTo": "PREVIOUS|NEXT|FIRST|LAST",
  "levelAddress": {
    "fieldCaption": "string",
    "function": "string"
  }
}

Moving calculation options

{
  "aggregation": "SUM|AVG|MIN|MAX",
  "previous": -2, // number of periods before current
  "next": 0, // number of periods after current
  "includeCurrent": true, // include current period
  "fillInNull": false // fill null values
}

Custom sort calculation options

Additionally, many table calculation types allow for a custom sort. You can define another field in the query to sort on.
Custome Sort dialog

{
  "customSort": {
    "fieldCaption": "string",
    "function": "string",
    "direction": "ASC|DESC"
  }
}