On a report that has the report limit set, the results of a metric calculation appear to be incorrect when checking the metric values returned on a report. The metric definition contains analytical functions such as rank, average, percentile, etc… which are used to calculate the results.
For example, consider the following report that uses a percentile metric with no report limit:
A check on the following calculation using the numbers returned on the report does indeed produce the correct results:
PERCENTILE ({46750, 65340, 121600, 270900},0.9) = 226,110
Now, consider the same report using a report limit to narrow the result set to Revenue > $50,000:
The following calculation using the numbers returned on the report IS NOT correct:
PERCENTILE ({65340, 121600, 270900},0.9) = 226,110
The following calculation IS correct:
PERCENTILE ({65340, 121600, 270900},0.9) = 241,040
CAUSE
The reason for the inconsistency is that analytical engine calculates metrics such as percentile after getting the raw data back from the warehouse and also report limits are applied after the results have been calculated. In such cases, Report limits sometimes behave like a view filter . This makes the results of a calculation appear to be incorrect as compared to what is shown on the report. Instead of the percentile metric, if a normal metric is on the report, the HAVING clause on the calculated values is seen in the SQL. In the case of analytical metrics, since the calculation of the metric and application of the report limit are done by the analytical engine, the HAVING clause is not seen in the report SQL.
ACTION
To achieve the correct calculation, use a metric qualification in the report filter, rather than using a report limit to filter out the result set.
The following report uses a metric qualification in the report filter to narrow the result set to Revenue > $50,000:
Notice that the calculation returns the correct result using the numbers on the report.
PERCENTILE ({65340, 121600, 270900},0.9) = 241,040
