Aggregate Node: Roll Up Data to a Higher Level

Use an Aggregate node to roll up data to a higher granularity in Salesforce Data Pipelines. You can aggregate using: unique, sum, average, count, maximum, and minimum. You can group by any dimension or date column. You can also pivot dimensions as columns. For example, you can group opportunities by account to get account-level details, like total opportunity amount, average number of cases, and the average days to close an opportunity for each account. You can then pivot on account type to analyze each combination of account and account type.

User Permissions Needed
To manage and create a recipe:	Edit CRM Analytics Dataflows OR Edit Dataset Recipes

1. In a Data Prep recipe that already has at least one Input node, select the Add Node button () between two nodes or at the end of the recipe. To show the Add Node button between two nodes, hover the cursor on the connecting line.
2. In the Add Node dialog box, select Aggregate.
   
3. Add at least one aggregate.

   Before you can add a grouping, add an aggregate. You can select one of the following aggregate functions.

   Aggregate Function	Description
   Unique	Counts only unique values in the selected column.
   Sum	Calculates the total by adding all values in the selected column.
   Average	Calculates the average value of the selected column.
   Count	Counts all values in the selected column.
   Maximum	Calculates the maximum value of the selected column.
   Minimum	Calculates the minimum value of the selected column.
   Stddevp	Calculates the population standard deviation. Population standard deviation measures the spread of data distribution—the typical distance between each data point and the average.
   Stddev	Calculates the sample standard deviation. Sample standard deviation measures the spread of data distribution for a given sample. Use sample standard deviation to estimate the population standard deviation based on a sample.
   Varp	Calculates the population variance. Population variance indicates how spread out data points are in a specific population.
   Var	Calculates the sample variance. Sample variance indicates how spread out data points are for a given sample. Use sample variance to estimate the population variance based on a sample.

   
4. To group rows, add one or more dimension or date columns in the Group Rows field.

   For example, you can group by account name.

   
5. To pivot dimensions as columns, add up to two dimension columns in the Group Columns field, select the values for each dimension column, and click Apply. For direct datasets and datasets, you can search for all column values. Otherwise, you are limited to preview data. If a value doesn’t appear in the list, adjust your sample size or refine your search. Then, click Add new value and enter the value.

   For example, you can group columns by opportunity type. Each combination of opportunity type and aggregate appears as a separate column.

   
6. To add the Aggregate node to the recipe, click Apply.
7. Save the recipe.

Note Because pivoting increases the number of columns, keep in mind that the Aggregate node can create up to 5,000 columns. If the node exceeds the maximum, you can reduce the number of columns by changing the aggregates or row and column groupings.

Your values that aren’t picked are automatically included in an Other column when you pivot with the Data Prep aggregate node. For example, you can group columns by opportunity type, select your most important types, and let the rest be grouped as Other.