The main way to filter data is to use dimensions. After you filter the data by a dimension, another filtering option is available - you can filter by a condition.
Filter by a dimension
Dimensions are groups of data that you can use to filter the whole dataset. The dimensions that are available depend on the underlying database. They display in the panel on the left of the grid. For example:
In a Sales database you might have dimensions such as Customers, Products, Sales Reps, Regions, Vendors and so on.
In an Inventory database you might have dimensions such as Supplier, Warehouse, Country, and so on.
Some dimensions have a subset of dimensions grouped underneath them, for example, the might have the Product Class and Product Group might be subsets of the Product dimension.
Click a dimension to filter the data in the grid and see a list the entities in that dimension. The dimension button turns blue to identify it as the active dimension.
For example, suppose you are viewing your Sales database and want to take a closer look at your customers, to see which customers generate the most sales revenue. When you click the Customer dimension, a list of customers displays in the grid.
After you filter by one dimension and focus on some data, you can filter by another dimension. You can repeat this action to, in effect, filter by multiple dimensions and drill down further into your data.
View filtered data in the grid
When you filter the data by a dimension (and condition), the grid updates and looks and behaves different to the Summary view. The key characteristics are as follows:
The selected dimensions displays in blue.
Properties (dimension metadata, such as Name and Code) display as columns. If the data in the Code column is blue, it indicates a link is available. You can select other properties to display or create your own custom property via the Properties menu.
Measures (data values relating to dimensions) display as columns, for example the Total Revenue or Current column. By default, the data is sorted by the first measure column, in ascending order. You can then sort the data in other ways. You can change the measures and their format via the Measures menu.
Cells that do not contain data are shaded blue.
Negative figures display in red.
See Work with the grid to learn how to sort data, freeze properties, view the record count and more.
Filter by a condition
When you filter the data by a dimension, you can then filter both the text and numerical data in the grid using a condition.
Right-click a cell in the grid to view your options. The options that are available depend on the type of data. For example, if you right-click:
Some text in the Name column, you can filter the grid to display records with the same name (EQUALS condition) or similar name (LIKE condition).
A value in the Revenue column, you can filter the grid to display records that are less than, greater than or equal to that value.
A value in a Date column, you can filter the grid to display records that are less than, greater than or equal to that value or a different month. This option is useful as it allows you to make comparisons.
Example - Find customers with sales over a certain amount in a specific period
Suppose you are viewing your Sales data in Period mode and have filtered your data by the Customer dimension. You want to identify the customers with sales of around $20,000 or more in the month of January.
(Optional) Click the January column header to sort the data in the grid by that column, in ascending order. This action makes it easier to find a value to use as a starting point.
Identify a value of around 20,000 (20,161.20), then right-click that value and click GREATER THAN > 20,161.20.
(Optional) Click the January column header to sort the data in descending order, so you can confirm that the highest value is now 20,161.20.
The grid updates to display only the data that meets the specified criteria, giving you a list of customers whose sales for July were $20,161.20 or higher. Any totals or averages that display in the grid now relate only to the filtered data. The status bar above the grid identifies the filter and an asterisk displays on the corresponding dimension to signify that a filter has been applied.
This feature is not available in the following circumstances: Summary view, Transaction, Matrix share, and Market modes, Target % format and when the Average Value columns with the Totals as average option is selected. The option to view Transactions via this feature is not available in Market mode.
Remove a filter
The process to remove a filter depends on they action you have taken:
If you filter by one dimension and then want to filter by another dimension, so long as you have not selected anything in the grid, you can simply click the other dimension. You can quickly switch between dimensions and the grid updates accordingly.
If you filter by a dimension and select rows in the grid, then select another dimension, a filter is applied to the first dimension and the number of selected rows displays on that dimension button. Click the X on the dimension button to remove the first filter.
As mentioned above, if you filter by a condition, an asterisk displays on the corresponding dimension button. Click the X on the dimension button to remove the filter.
To completely reset the data, click the Reset button.