Considering Null Fact Values in Pivot Computed Items

For Simple and Weighted Moving Averages, if any values are missing/null in the source “Data Column”, the Moving Average functionality compensates by subtracting the number of instances of null values from the “Window” divisor. For example, consider the following example for the “Sales” value for “Feb” which is null:

Use Surface Value—Recalculates the values in the visible cells or surface of the pivot rather than the values in the Results section.

True Computed Item Totals—Recalculates break totals so that each total value is equal to the sum of their displayed detail cells. True computed item totals use aggregation according to the specified data function and does not rely on the computed item total formula. If you disable this option, the break total cell values are derived from the computed item formula applied to the detail cell. Average and Count aggregation Data Functions are not evaluated in True Total mode unless you enable Use Surface Values. If you do not enable Use Surface Values, the Average and Count aggregation are calculated using the count of the underlying Table/Result section data values instead of the displayed Pivot values.

Enable Null Facts In Computed Items—Evaluates a null fact value (an empty cell value) as a zero fact value for non-Moving functions. For Moving Function calculations, where the presence of all displayed Fact cell must be considered in calculations, the following behavior occurs when null fact values are considered as zero. The default option to evaluate and show null values is disabled.

For Simple and Weighted Moving Averages, if any values are missing/null in the source “Data Column”, the Moving Average functionality compensates by subtracting the number of instances of null values from the “Window” divisor.

For Moving Maximum, Moving Minimum, Moving Difference, Moving Sum and Moving Median functions moving functions, if any values are missing/null in the source “Data Column”, the missing source values are omitted and the calculation Window size “shrunk” correspondingly.

Exponential Moving Averages treat missing source Fact values as having a value of zero.

For all Moving Functions, except Exponential Moving Averages, if all the values in the particular calculation Window are null or zero, the Moving Function value is also, by default, null or zero.

For all Moving Functions, except Exponential Moving Averages, if the Moving Function calculation Window size exceeds the available number of input fact values, the returned set of Moving Function values should be null.

When this option is disabled, the Pivot sections that receive their data from a relational Query section, ignore Fact cells with null data when calculating Computed Items. That is, the calculation is simply skipped for the null cell.