Table Calculation Query Examples

🕐 5 min read

Example 1: Rank profit by region and year
Example 2: Percent of total profit by region and year
Example 3: Running total profit by region and year
Example 4: Difference from previous year’s profit
Example 5: Moving average profit (three-year window)
Secondary table calculations

Example 1: Rank profit by region and year

This example ranks profit values within each region-year combination.

"query": {
    "fields": [
      {
        "fieldCaption": "Region"
      },
      {
        "fieldCaption": "Order Date",
        "function": "YEAR"
      },
      {
        "fieldCaption": "Sales",
        "function": "SUM"
      },
      {
        "fieldCaption": "Profit",
        "function": "SUM"
      },
      {
        "fieldCaption": "Profit",
        "function": "SUM",
        "tableCalculation": {
          "tableCalcType": "RANK",
          "dimensions": [
            {
              "fieldCaption": "Region"
            },
            {
              "fieldCaption": "Order Date",
              "function": "YEAR"
            }
          ],
           "rankType": "COMPETITION"
        }
      }
    ]
  }

In the response, each region-year combination will have profit values ranked from highest to lowest.

Example 2: Percent of total profit by region and year

This example shows what percentage each profit value represents of the total profit for that region-year.

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

In the response, each profit value will be shown as a percentage of the total profit for that specific region and year.

Example 3: Running total profit by region and year

This example shows cumulative profit over time for each region.

"query": {
    "fields": [
        {
            "fieldCaption": "Region"
        },
        {
            "fieldCaption": "Order Date",
            "function": "YEAR"
        },
        {
            "fieldCaption": "Sales",
            "function": "SUM"
        },
        {
            "fieldCaption": "Profit",
            "function": "SUM"
        },
        {
            "fieldCaption": "Profit",
            "function": "SUM",
            "tableCalculation": {
                "tableCalcType": "RUNNING_TOTAL",
                "dimensions": [
                    {
                        "fieldCaption": "Region"
                    },
                    {
                        "fieldCaption": "Order Date",
                        "function": "YEAR"
                    }
                ],
                "restartEvery": {
                    "fieldCaption": "Order Date",
                    "function": "YEAR"
                }
            }
        }
    ]
}

Example 4: Difference from previous year’s profit

This example shows how much profit changed compared to the previous year.

"query": {
  "fields": [
    {
      "fieldCaption": "Region"
    },
    {
      "fieldCaption": "Order Date",
      "function": "YEAR"
    },
    {
      "fieldCaption": "Sales",
      "function": "SUM"
    },
    {
      "fieldCaption": "Profit",
      "function": "SUM"
    },
    {
      "fieldCaption": "Profit",
      "function": "SUM",
      "tableCalculation": {
        "tableCalcType": "DIFFERENCE_FROM",
        "dimensions": [
          {
            "fieldCaption": "Order Date",
            "function": "YEAR"
          }
        ],
        "relativeTo": "PREVIOUS"
      }
    }
  ]
}

The response shows the absolute difference in profit compared to the previous year for each region.

Example 5: Moving average profit (three-year window)

This example shows a three-year moving average of profit for each region.

  "query": {
    "fields": [
      {
        "fieldCaption": "Region"
      },
      {
        "fieldCaption": "Order Date",
        "function": "YEAR"
      },
      {
        "fieldCaption": "Sales",
        "function": "SUM"
      },
      {
        "fieldCaption": "Profit",
        "function": "SUM"
      },
      {
        "fieldCaption": "Profit",
        "function": "SUM",
        "tableCalculation": {
          "tableCalcType": "MOVING_CALCULATION",
          "dimensions": [
            {
              "fieldCaption": "Region"
            },
            {
              "fieldCaption": "Order Date",
              "function": "YEAR"
            }
          ],
          "aggregation": "SUM",
          "previous": -2,
          "next": 1,
          "includeCurrent": true
        }
      }
    ]
  }

Secondary table calculations

There is an option to add secondary table calculations.

Order of operations

Primary calculation is applied first
Secondary calculation is applied to the results of the primary calculation

Supported combinations

RUNNING_TOTAL can have any secondary calculation
MOVING_CALCULATION can have any secondary calculation
Other table calculation types do not support secondary calculations

Use cases

Smoothing: Apply moving average to running totals for trend analysis
Ranking: Rank the results of running totals or moving averages
Normalization: Convert running totals to percentages or percentiles
Growth Analysis: Show growth rates of cumulative values

Common patterns

Running Total + Percent of Total: Show cumulative contribution to total
Moving Average + Rank: Rank smoothed values
Running Total + Difference: Show growth of cumulative values
Moving Average + Percentile: Show relative position of smoothed values

Example: Running total with percent difference secondary calculation

This example shows how to first calculate running totals, then show the percentage change in running totals.

  "query": {
    "fields": [
      {
        "fieldCaption": "Region"
      },
      {
        "fieldCaption": "Order Date",
        "function": "YEAR"
      },
      {
        "fieldCaption": "Sales",
        "function": "SUM"
      },
      {
        "fieldCaption": "Profit",
        "function": "SUM"
      },
      {
        "fieldCaption": "Profit",
         "function": "SUM",
        "tableCalculation": {
          "tableCalcType": "RUNNING_TOTAL",
          "dimensions": [
            {
              "fieldCaption": "Region"
            },
            {
              "fieldCaption": "Order Date",
              "function": "YEAR"
            }
          ],
          "aggregation": "SUM",
          "secondaryTableCalculation": {
            "tableCalcType": "PERCENT_DIFFERENCE_FROM",
            "dimensions": [
              {
                "fieldCaption": "Region"
              },
              {
                "fieldCaption": "Order Date",
                "function": "YEAR"
              }
            ],
            "relativeTo": "PREVIOUS"
          }
        }
      }
    ]
  }