Moving Total dialog

Use this dialog to create a moving total calculation. A moving total calculation calculates a total for the specified number of rows or a specified time period before each value. For example, you might want to a three month moving average of sales figures.

This type of analysis is sometimes referred to as windowing analysis.

For more information, see:

"What are analytic functions?"

"Examples of using row-based and time-based intervals"

"How to create a new calculation using an analytic function template"

"Windowing function examples"

Total on

Use this drop down list to choose the item for which you want to calculate the moving total. For example, you might choose Sales SUM to calculate a moving total of sales values.

Total type

Use this drop down list to specify what type of total you want. For example, choose 'f(x) Sum' to add values in the group, or 'f(x) Avg' to produce an average of the values in the group.

Start on

Use this drop down list to specify the number of rows or the number of time periods in which you want to compare the values.

You specify a number of rows or a time period before the current value. For example, you might choose 2 to calculate the total from two values before each value, including the current value. In other words, you choose 2 if you want a three month moving average.

Use the adjacent drop down list to choose a row-based interval or a time-based interval. For example, you might choose Months Before Current Value if you want a three month moving average.

Hint: If you have time-based data, use time-based groups (e.g. Days/Weeks/Months Before Current Value). If you do not have time-based data, use row-based groups (e.g. Rows Before Current Value). For more information about types of group, see "About windowing".

For examples of specifying row-based and time-based intervals, see "Examples of using row-based and time-based intervals".

Order rows by

Use this drop down list to specify how to order the values. For example, you might choose Calendar Month if you want a three month moving average.

Use the adjacent drop down list to specify whether to order values in ascending order (i.e. Lowest to Highest) or descending order (i.e. Highest to Lowest).

Hint: If you have specified a time-based group in the Start on drop down list, specify a time-based Order rows by value (e.g. Calendar Month).

Then order rows by

Use this drop down list to specify a secondary order for the values. For example, you might choose City to sort values on city within month.

Use the adjacent drop down list to specify whether to order values in ascending order (i.e. Lowest to Highest) or descending order (i.e. Highest to Lowest).

Note: This drop down list is only enabled if you specified a row-based group in the Start on drop down list. This drop down list is only disabled if you specified a time-based group in the Start on drop down list.

Restart moving total at each change in

Use this list to specify which groups to use if you want to arrange results into groups (sometimes referred to as partitions).

For example, to calculate a three month moving average for Sales SUM values within a give year and region, you might choose Calendar Year and Region.

If you do not specify a group, Discoverer treats all worksheet values as a single group.

Calculation

calculation When you click OK, the formula is transferred to the Calculation field in the "New Calculation dialog" or the "Edit Calculation dialog".