View filter effects on metrics with relative functions

Metrics with relative functions (OLAP functions and Rank and Ntile functions) such as RunningSum or Rank return values that are relative to other values on a report. When data is restricted by view filter qualifications, some of the report data is hidden from view while the data available is not changed. This can cause the values returned by metrics with relative functions to appear to have incorrect values for the data displayed on the report.

For example, consider a report with Customer Group, Year, Average Net Sales Order Amount per Customer, and RunningAvg (Average Net Sales Order Amount per Customer), as shown below.

You then create a view filter to restrict data to only years 2007 and 2008. The view filter qualification and resulting report are shown below.

Notice that the values for the RunningAvg (Average Net Sales Order Amount per Customer) metric do not change, only the 2006 values are hidden. The values displayed do not accurately reflect the view of data and instead reflect the data available for the entire report, including the 2006 data hidden from view.

This is because the view filter is calculated without re-executing SQL against the data warehouse, and is evaluated after calculating the metrics on the report. Therefore, the metric is not recalculated to reflect the view of data shown on the report as restricted by the view filter.

If you plan to use metrics with relative functions and require them to reflect the data displayed on the report, you can use one of the options summarized below:

• Use report filters rather than view filters. A report filter causes a report to re-execute its SQL against the data warehouse, which can cause more processing time than a view filter. However, this allows a metric with a relative function to recalculate its values based on the filtering criteria.
• Use derived metrics with relative functions rather than standard metrics. Derived metrics can be evaluated after view filters and thus reflect the view of data on a report without having to re-execute SQL. However, derived metrics cannot be saved for use in multiple reports and can require modification to work as intended with view filters. For information on the interaction between view filters and derived metrics, see View filter effects on derived metrics.