groupByTable

Suppose you want to know how each region performed for total profit. You can group the rows by Region and then sum-aggregate the Profit values for each group.

The ATL is: [[groupByTable(WholeTable, Region, Profit, sum())]].

The output is this list of lists:

Each inner list contains values for a distinct group. For each inner list, the first value is the region name and the second is the sum-total profit. The list is sorted by the first aggregated value in descending order.

The printed output is:

Here's how you might extract values from the result to produce narrative text:

[[global.regions = groupByTable(WholeTable,Region,Profit,sum()); global.topOne = top(regions,1);'']]

The best performing region was [[topOne[0][0]]], with profits of [[currencyFormat(topOne[0][1])]].

[[sorted = sort(regions, (x,y) -> sign(x[1] - y[1])); global.bottomOne = top(sorted,1);'']]

The worst performing region was [[bottomOne[0][0]]], with profits of [[currencyFormat(bottomOne[0][1])]].

The printed result is:

Sometimes you need to specify multiple aggregation columns and multiple aggregation types. Suppose you want to produce a narrative that identifies (1) the regions that achieved average sales over $50K, and (2) the number of branches in each successful region.

To calculate average sales, you must group by Region and mean-aggregate the Sales values for each group. To calculate the number of branches in each region, you must group by Region and then apply count() aggregation to the BranchID column.

The ATL is [[groupByTable(WholeTable, Region, (Sales,BranchID), (mean(),count()))]].

The output is this list or lists:

The inner list contains values for a distinct group. For each inner list, the first value is the region name, the second is the average sales figure, and the third is the number of unique branches in the group.

The printed output is:

Here's how you might extract values from the result to produce narrative text:

[[global.over = filter(groupByTable(WholeTable,Region,(Sales,BranchID),(mean(),count())), x -> x[1] >= 50000);'']]

[[global.regionsOver = map(over, x -> x[0]);'']]

[[regionsOver]] were the only regions with average sales over $50K.

[[over[0][0]]] averaged [[currencyFormat(over[0][1])]] from [[numToWords(over[0][2],'',10)]] branches.

[[over[1][0]]] averaged [[currencyFormat(over[1][1])]] from [[numToWords(over[1][2],'',10)]] branches.

The printed result is:

The previous examples use a single grouping column. This example demonstrates the benefits of using two or more columns (second parameter) to make your grouping criteria more precise.

Suppose you want to group by Region and Type. Also, suppose that you wish to discover (1) the Region + Type group with the highest sales, and (2) the Region + Type group with the highest average profit.

The ATL is [[groupByTable(WholeTable, (Region,Type), (Sales,Profit), (sum(),mean()))]].

The output is this list of lists:

Each inner list contains values for a distinct group. For each inner list, the first value is the region name, the second is the store type, the third is the sum-total for sales, and the fourth is the mean profit.

The printed output is:

Here's how you might extract values from the result to produce narrative text:

[[global.results = groupByTable(WholeTable,(Region,Type),(Sales,Profit),(sum(),mean())); global.topSales = top(results,1);'']]

[[topSales[0][1]]] stores in [[topSales[0][0]]] achieved the best total sales ([[abbreviateNumber(currencyFormat(topSales[0][2]))]]).

[[sortedByProfit = sort(results, (x,y) -> sign(y[3] - x[3])); global.topProfit = top(sortedByProfit,1);'']]

[[topProfit[0][1]]] stores in [[topProfit[0][0]]] achieved the best average profit ([[abbreviateNumber(currencyFormat(topProfit[0][3]))]]).

The printed result is: