Running Total Analytic Function

We are using US Census data to create a visual that uses the Running Total analytic function.

Availability Note. This feature is not available when using the following data connection types: MySQL, SQLite, and MariaDB.

To use the Running Total analytic function with US Census data, follow these steps. The general steps are here.

Developer Note. If you do not have access to the US Census dataset, download the census-pop.csv.zip file, uncompress it, import the data into Arcadia, and create the dataset.
  1. The input set up for the Running Total visual follows:

    • X shelf: state
    • Y shelf: sum(population), for now
    • Colors shelf: year
    • [Optional] Tooltips shelf: sum(population), sum(year)
    • Filters shelf: exclude the following years of the census: 1790 and 1800.

      There is insufficient data for these years, and it skews the results.

    shelves for analytic functions
  2. On the Y shelf, click the icon to the right of the variable.

    field options when on shelf
    Accessing Field Options
  3. In the dropdown, hover over Analytic Functions.

    analytic functions menu
    Analytic Functions Options
  4. From the Analytic Functions menu, select Running total.
  5. In the Analytic Function: Running Total modal window,

    • In Select aggregate for this measure, leave the default Sum
    • Under Select entities over which to take running total, select state
    • Under Select the sort order, click the (add/plus) icon, select year, and then set it to ascending sort order.

      Note that you can add additional sort parameters, by clicking the (add/plus) icon.

    Analytic Function: Running Total Interface
    Analytic Function: Running Total Interface
  6. Click Apply.
  7. Click Refresh Visual.

    running total for state populations over time
    Running Total for State Populations over Time; Stacked Bar Chart
  8. Note the expression used for calculating the running total:

    sum(sum([population])) over (partition by [state] order by [year] rows 
      between unbounded preceding and current row) as 'running total(population)'
    Viewing the Running Total Expression
    Viewing the Running Total Expression

General Instructions for Running Total Analytic Function

Follow these general steps to create a running total visual:

  1. When creating or editing a visual, determine for which field variable you must apply an analytic function.

    The shelves that support analytic functions depend on the visual type, and are typically available for X and Y axes, Measures, Dimensions, Colors, and Tooltips.

  2. Click the icon to the right of the variable.

    field options when on shelf
    Accessing Field Options
  3. In the dropdown, click Analytic Functions.

  4. From the Analytical Functions menu, select Running total.

    The Analytic Function: Running Total modal window appears.

    Analytic Function: Running Total Interface
    Analytic Function: Running Total Interface
  5. In the Analytical Function: Running Total modal window, in Select aggregate for this measure, choose one of:

    • Sum [default]
    • Count
    • Approx Distinct Count
    • Exact Distinct Count
    • Minimum
    • Maximum
    • Average
  6. [Optional] Under Select entities over which to take running total, select one or more entities listed in the modal window.
  7. [Optional] Under Select the sort order, specify the sort order. If you don't specify it, the current column's aggregate is the sort order.

    • Click the (add/plus) icon to add the sort field; choose between None and a few pre-selected options (what is already on the shelves of the visual).
    • Specify either (ascending) [default] or (descending) sort order.
    • To add more sorting categories, click the (add/plus) icon and specify the new sort mechanism.
  8. Click Apply.
  9. Click Refresh Visual.