Custom Table Calculations


🕐 3 min read

Custom table calculations allow you to write your own Tableau calculation formulas using the CUSTOM table calculation type. This provides the flexibility to create complex calculations that go beyond the standard quick table calculations available in Tableau Desktop.
For more information, see the Tableau documentation for:

Write a table calculation in the “calculation” field of the Table Calculation Field and give it a name.

For example:

"query": {
    "fields": [
        {
            "fieldCaption": "Region",
            "sortPriority": 1
        },{
              "fieldCaption": "Segment",
              "sortPriority": 2
        }, {
              "fieldCaption": "Order Date",
              "function": "YEAR",
              "sortPriority": 3
        }, {
            "fieldCaption": "MyDifferenceCalc",
            "calculation": "ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)",
            "tableCalculation": {
                "tableCalcType": "CUSTOM",
                "dimensions": [
                    {
                        "fieldCaption": "Region"
                    }, {
                        "fieldCaption": "Segment"
                    }, {
                        "fieldCaption": "Order Date",
                        "function": "YEAR"
                    }
                ]
            }
        }
    ]
  }

The preceding example does the following:

The result shows the difference in sales from the previous period.

Common custom table calculation patterns

Difference from previous

This example calculates the absolute difference from the previous value, using the formula, ZN(SUM(\[Sales\])) - LOOKUP(ZN(SUM(\[Sales\])), -1).

{
  "fieldCaption": "Sales Difference",
  "calculation": "ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)",
  "tableCalculation": {
    "tableCalcType": "CUSTOM",
    "dimensions": [
      {
        "fieldCaption": "Region"
      },
      {
        "fieldCaption": "Order Date",
        "function": "YEAR"
      }
    ]
  }
}

Percent change

This example calculates the percentage change from the previous value, using the formula: (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / LOOKUP(ZN(SUM([Sales])), -1).

{
  "fieldCaption": "Sales % Change",
  "calculation": "(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / LOOKUP(ZN(SUM([Sales])), -1)",
  "tableCalculation": {
    "tableCalcType": "CUSTOM",
    "dimensions": [
      {
        "fieldCaption": "Region"
      },
      {
        "fieldCaption": "Order Date",
        "function": "YEAR"
      }
    ]
  }
}

Year-over-year growth

This example calculates year-over-year growth, using the formula, SUM([Sales]) - LOOKUP(SUM([Sales]), -4)) / LOOKUP(SUM([Sales]), -4).

{
  "fieldCaption": "YoY Growth",
  "calculation": "(SUM([Sales]) - LOOKUP(SUM([Sales]), -4)) / LOOKUP(SUM([Sales]), -4)",
  "tableCalculation": {
    "tableCalcType": "CUSTOM",
    "dimensions": [
      {
        "fieldCaption": "Region"
      },
      {
        "fieldCaption": "Order Date",
        "function": "YEAR"
      }
    ]
  }
}

Running total

This example calculates cumulative totals, using the formulat, RUNNING_SUM(SUM([Sales])).

{
  "fieldCaption": "Running Total Sales",
  "calculation": "RUNNING_SUM(SUM([Sales]))",
  "tableCalculation": {
    "tableCalcType": "CUSTOM",
    "dimensions": [
      {
        "fieldCaption": "Region"
      },
      {
        "fieldCaption": "Order Date",
        "function": "YEAR"
      }
    ]
  }
}

Moving average

This example calculates a three-period moving average, using the formula, WINDOW\_AVG(SUM(\[Sales\]), \-2, 0\).

{
  "fieldCaption": "Moving Average Sales",
  "calculation": "WINDOW_AVG(SUM([Sales]), -2, 0)",
  "tableCalculation": {
    "tableCalcType": "CUSTOM",
    "dimensions": [
      {
        "fieldCaption": "Region"
      },
      {
        "fieldCaption": "Order Date",
        "function": "YEAR"
      }
    ]
  }
}