🕐 5 min read
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"
}
}
}
]
}