Moving Average Analytic Function

We are using US Census data to create a stacked bar visual that uses Moving Average analytic function.

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

To use the Moving Average analytic function with data in US Census dataset, follow these steps in the Example following.

Developer Note. If you do not have access to the US Census dataset, download the census-pop.csv.zip file, uncompress it, Importing Data into Arcadia, and Creating Datasets.
  1. The input set up for the Moving Average visuals follows:

    • X shelf: state
    • Y shelf: sum(population), for now
    • Colors shelf: year
    • [Optional] Tooltips shelf: sum(population), sum(year)
    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 Moving average.
  5. In the Analytic Function: Moving Average modal window,

    • In Select aggregate for this measure, leave the default Sum.
    • Under Select entities over which to create moving averages, 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.

    • Under Rows to include in the average, the options for leading rows are: All previous rows, Current row, or Previous row(s). Select the last option, then specify the number of preceding rows for the calculation, 3.

      Similarly, the options for following rows are: All forward rows, Current row, or Forward row(s). Select the last option, and specify 3 rows.

    analytic function moving average menu
    Analytic Function: Moving Average Interface
  6. Click Apply.
  7. Click Refresh Visual.

    moving average of state populations over time
    Moving Average for State Populations over Time; Stacked Bar Chart
  8. Note the expression used for calculating the moving average. You can see it in the Enter/Edit Expression window modal:

    avg(sum([population])) over (partition by [state] order by [year] rows 
      between 3 preceding and 3 following) as 'moving average(population)'
    enter/edit expression interface
    Viewing the Moving Average Expression

Moving Average Analytic Function

Follow these general steps to create a visual that shows Moving Average:

  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 Analytic Functions menu, select Moving average.

    Viewing the Moving Average Expression
    Analytic Function: Moving Average Interface
  5. In the Analytic Function: Moving Average 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 create moving averages, 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. Under Rows to include in the average,

    • Specify the number of leading rows to capture in calculating the moving average. The options for leading rows are: All previous rows, Current row, or Previous row(s). Selecting the last option allows you to specify the number of leading rows for the calculation.
    • Specify the number of following rows to capture in calculating the moving average. Similar to the "leading" options, the options for following rows are: All forward rows, Current row, or Forward row(s). Selecting the last option allows you to specify the number of following rows for the calculation.

    Note: Early versions of PostgreSQL (before v9) support only "unbounded preceding", "unbounded following" and "current rows".

  9. Click Apply.
  10. Click Refresh Visual.