Help Centre

Pivot tables
Filter bars allow you to filter down the data in your pivot, to show specific ranges or categories.
Example filters

Show data from Americas and Asia only

Show years from 2005 to present

Show only business from Acme group companies

Add filters
  Click ‘Select field’ and choose from the list of fields in your data source.

  Select the operation. By default it is set to ‘Equals’.
Enter the filter value. A list of the values in the selected category field – select from this or enter free text or values.

Delete filters
Click the delete button [ – ] at the right end of the filter bar.
Toggle filters
Click the check box at the left end of the filter bar to toggle filters on and and off.
Filtering value fields
When filtering a value field, the filter applies to the raw data, not the aggregated value displayed in the pivot table.
Example filters

Show number claims greater than $20m by region
‘Claims’ ‘Greater than’ ‘20000000’
Aggregate by count
Segment by region
Show total value of claims greater than $20m by region
Claims’ ‘Greater than’ ‘20000000’
Aggregate by sum
Segment by region
Note that the filter entry field does not support commas in numbers.
Filter order
Filters apply cumulatively, from top to bottom.
Show only Engineering and Liability Lines of Business.

In the incorrect example, the ‘Engineering’ filter applies over the ‘Liability’ filter, filtering out all the data.
Advanced filters
Not empty
Not empty filters out rows in categories where the value is empty.

If we configure a pivot with category ‘Policy’, and values ‘Premium’ and ‘Claim’, we can hide Policies which do not contain a claim.
Showing top and bottom
Show only the top or bottom n results of the pivot query, e.g. Top 10 Claims by Line of Business.
 Select the category to filter by

 Select the operation Top or Bottom

 Enter the number of results, e.g. Top 5

 Add further segmentation if required by adding more categories

Where only one category and value are shown, results are sorted by magnitude (large to small). Where more than one category is shown results are sorted alphabetically (or chronologically) by category.
Multiple values can be added to a Top/Bottom filtered table. The filter will only apply to the left hand value column.
The Rank filter shows items of a specific rank or range of ranks, for example the fifth largest, or the sixth to tenth largest items.

Configure a pivot of Sub Regions and Claims. Show the Sub-Region with 5th highest total Claims.
Now show the Sub-Regions with total Claims ranked 6–10 by magnitude.