We are using US Census
data to create a stacked bar visual that uses Moving Average analytic function.
To use the Moving Average analytic function with data in US Census
dataset,
follow these steps in the Example following.
US Census
dataset, download the census-pop.csv.zip file, uncompress it, Importing Data into Arcadia, and Creating Datasets.The input set up for the Moving Average visuals follows:
state
sum(population)
, for nowyear
sum(population)
, sum(year)
On the Y shelf, click the icon to the right of the variable.
In the dropdown, hover over Analytic Functions.
In the Analytic Function: Moving Average modal window,
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.
Click Refresh Visual.
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)'
Follow these general steps to create a visual that shows Moving Average:
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.
Click the icon to the right of the variable.
In the dropdown, click Analytic Functions.
From the Analytic Functions menu, select Moving average.
In the Analytic Function: Moving Average modal window, in Select aggregate for this measure, choose one of:
[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.
Under Rows to include in the average,
Note: Early versions of PostgreSQL (before v9) support only "unbounded preceding", "unbounded following" and "current rows".