Difference dialog

Use this dialog to create a difference calculation. A difference calculation typically calculates the change in values across time. For example, you might want to calculate the change in sales from the previous month.

This type of analysis is sometimes referred to as LAG/LEAD analysis.

Note: If you want to calculate the change in value over time as a percentage, use the Percent Difference calculation (for more information, see "Percent Difference dialog").

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"

"LAG/LEAD function examples"

Compare values of

Use this drop down list to choose which item you want to analyze. For example, you might choose Sales SUM to calculate the change in sales totals over time.

Preceding value

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. For example, you might choose '3' to calculate the difference in sales compared to three months previously.

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' to compare sales values with earlier sales values.

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 worksheet values. For example, you might choose Calendar Month to compare sales with the previous months sales.

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 Preceding value drop down list, specify a time-based item (e.g. Calendar Month) in the Order rows by field.

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. Then 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 Preceding value drop down list. This drop down list is only disabled if you specified a time-based group in the Preceding value drop down list.

Restart calculation 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 the difference in Sales SUM values within year, you might choose Calendar Year.

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".

Notes

A difference calculation will return a null value if there is no preceding value with which to compare.