Cumulative formulas allow you to calculate the average, maximum, minimum, or sum of your data over time or any other sequential data set. Each cumulative formula accepts a measure and one or more optional grouping attributes (such as region or campus):
formula (measure, [attribute, attribute, ...])
Only the measure value is required. If you supply both a measure and attributes, the formula returns the aggregate of the measure accumulated by the attribute(s) in the order specified.
Cumulative formulas reset their results for different values of any attribute in your search that is not included in your cumulative formula. For example, if you use cumulative_sum (amount, date) but also include fund name in your search, the running total will reset for every new fund type.
Available Cumulative Formulas
cumulative_average: Returns the average of the measure, accumulated by the attribute(s) in the order specified.Example:
cumulative_average (amount, campus name, fund name)
cumulative_max: Returns the maximum value of the measure encountered so far, accumulated by the attribute(s).Example:
cumulative_max (amount, fund name)
cumulative_min: Returns the minimum value of the measure encountered so far, accumulated by the attribute(s).Example:
cumulative_min (amount, campus name)
cumulative_sum: Returns the running total of the measure, accumulated by the attribute(s).Example:
cumulative_sum (amount, first name)
Calculate a Cumulative Sum
This example demonstrates using the cumulative_sum formula, also known as a running total.
Start a new search in the chart builder.
Click + Add and select Formula.
Enter the
cumulative_sumformula, providing a measure and one or more attributes. For example, to see a running total of the net amount of donations by campus name:cumulative_sum (net amount, campus name)
Name the formula (e.g., "Cumulative Sum Formula") and select Save.
The formula will now appear as its own column in your table or a line in your chart.
Calculate a Cumulative Sum of a Unique Count
While there isn't a single "cumulative unique count" function, you can achieve this by combining cumulative_sum with group_aggregate. This allows you to pass a unique count into the windowing function.
For example, to calculate the cumulative sum of unique customer names by month:
cumulative_sum (group_aggregate (unique count (Customer Name), query_groups (), query_filters ()), start_of_month (Date))
This syntax produces a unique count for each individual result (e.g., Oct 2021). It does not "de-duplicate" across results; if a donor appears in both October and November, they are counted in both months' aggregates.