Use this dialog to create a percent difference calculation. A percent difference calculation typically calculates the difference between an initial value and a subsequent value, and expresses the result as a percentage. For example, you might want to calculate the difference between sales in one month and sales in another month expressed as a percentage.
This type of analysis is sometimes referred to as LAG/LEAD analysis.
Note: To calculate just difference between an initial value and a subsequent value, use the Difference calculation (for more information, see "Difference dialog").
For more information, see:
"What are analytic functions?"
"Examples of using row-based and time-based intervals"
"How to create a calculation using an analytic function template"
Use this drop down list to choose the item for which you want to calculate the percentage difference. For example, you might choose Sales SUM to calculate the change in sales values over time.
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 (for example, Days/Weeks/Months Before Current Value). If you do not have time-based data, use row-based groups (for example, 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".
Use this drop down list to specify how to order the worksheet values. For example, you might choose Calendar Month to compare sales in one month with sales in other months.
Use the adjacent drop down list to specify whether to order values in ascending order (that is, Lowest to Highest) or descending order (that is, Highest to Lowest).
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 (that is, Lowest to Highest) or descending order (that is, Highest to Lowest).
Restart calculation at each change in
Use this list to specify the groups to be used 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.
This read-only field displays the underlying formula that you are building as you specify values for the fields above. This formula is updated each time you change one of the values in the fields above. When you click OK, the formula is transferred to the Calculation field in the "New Calculation dialog" or the "Edit Calculation dialog".
A percent difference calculation returns a null value if there is no preceding value with which to compare.