Zoomdata Version

Pivot Table Totals and Aggregate (Group) Filters

Symptom

When you add an aggregate (group) filter to a pivot table, all the totals in the table display as N/A.

The only pivot tables for which this is not a problem are pivot tables created using fused data from Fusion data sources. This is because Fusion data sources are processed using a newer version of the Zoomdata query engine than other Zoomdata data sources at this time.

Cause

Pivot tables separate the queries for the main table area, for row totals, column totals, and for the grand total. In most cases, these queries are independent of one another and are treated independently.

However, when you add an aggregate filter to the pivot table query, the filter is applied only to the query used for the main table area, and some records are filtered out. But the pivot table total queries do not include the aggregate filter and still calculate totals for the whole table, including the records that were filtered out in the main table area. The reason for this is that the metric specified in the main table area filter is different than the metrics specified by the row, column, and total queries, because the data grouping is different for each query.

So the data in main area of the table and the totals will not correlate (the totals will include the values filtered out by the aggregate filter), possibly leading to misinterpretation of the data. For example, you might see cells of metrics that add up to 1000, but the total for the same column will be calculated as 2000.

To prevent this, pivot tables with aggregate filters present all totals as N/A.

Was this topic helpful?