Cumulative functions

Cumulative formulas are aggregate formulas.

Cumulative formulas allow you to calculate the average, max, min, or sum of your data over time. Although we usually talk about cumulative formulas over time, you can use them over any other sequential data. Each of the cumulative formulas accepts a measure and one or more optional grouping by an attribute (like region or department):

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. You should experiment with only a measure and then with an attribute to see which output best meets your use case.

The cumulative formulas are:

cumulative_average

Takes a measure and one or more attributes. Returns the average of the measure, accumulated by the attribute(s) in the order specified.

Example
cumulative_average (revenue, order date, state)
cumulative_max

Takes a measure and one or more attributes. Returns the maximum of the measure, accumulated by the attribute(s) in the order specified.

Example
cumulative_max (revenue, state)
cumulative_min

Takes a measure and one or more attributes. Returns the minimum of the measure, accumulated by the attribute(s) in the order specified.

Example
cumulative_min (revenue, campaign)
cumulative_sum

Takes a measure and one or more attributes. Returns the sum of the measure, accumulated by the attribute(s) in the order specified.

Example
cumulative_sum (revenue, order date)

You may have attributes in your search that are not part of the cumulative formula. The cumulative formula resets its results for different values of any attribute that is not part of your cumulative formula. For example, with a cumulative formula of cumulative_sum (revenue, order date), if your search includes the attribute market segment, the cumulative sum formula aggregates the revenue by date separately for different market segments. For example:

Cumulative sum example

Calculate a cumulative sum

This example demonstrates using the cumulative_sum formula, also known as a running total. To use the cumulative function in a search:

  1. Start a new search.

    aggregation answer

  2. In the upper-right side of the table, click the icon ellipses (more options) menu icon and select Add formula.

    create formula in answer

  3. Enter the cumulative_sum formula, providing a measure and one or more attributes.

    This example returns the sum of yearly income, accumulated by the date.

    cumulative sum formula

  4. Name the formula by clicking its title and typing a new name.

  5. Click Save.

    The formula appears in the search bar and in the table as its own column.

    cumulative sum table

    A headline box that displays the cumulative sum within the entire table appears at the bottom. You can click it to toggle between different aggregations.

Related information

Was this page helpful?
×