Workbook Visualization Filters
After you set up a table view, pivot table, or chart, you can filter the data that is displayed using several filter types: value-based, condition-based, measure-based, or date filters.
In table views and pivot tables, value-based filters are applied to every row and column and removes those that do not contain the selected value. Similarly, in charts, any columns, bars, or lines that do not contain the selected value are removed from the results.
In pivot tables, measure-based filters are applied to the row and column totals and remove any columns or rows that do not match the selected filter criteria. In charts, any columns, bars, or lines with totals that do not meet the selected criteria are removed. Measure-based filters are available in measure fields, also known as fields that require an aggregate function such as sum, count, average, and so on. Therefore, measure-based filters apply to charts and pivot tables only.
In all workbook visualizations, you can also create filters based on specific dates and conditions. In date fields, you can filter results by adding specific dates or select dynamic dates that update when the workbook is run. In numeric fields, you can add condition-based filters to show results that match the numeric range that you define.
There is no limit on how many value-based filters you can apply to a workbook visualization, however you can only apply one measure-based or date-based filter. Additionally, if you apply both value-based and measure-based filters, all value-based filters are applied first.
Filters that you apply to a workbook visualization only refine the results displayed in the selected visualization and do not affect the dataset that the visualization is based on. To filter the dataset, you must create criteria filters from the Dataset Builder. Additionally, if you are in a NetSuite account that has values in multiple currencies or a NetSuite OneWorld account with multiple subsidiares, you must convert or consolidate these values before you can use them in a measure-based filter condition.
For more information about filtering a dataset, see Dataset Criteria Filters.
If you are working in a visualization based on linked datasets, you must apply dataset criteria filters to the matching field in each dataset. If you do not, your results might include values that you wanted to filter. For more information, see Create Visualizations Based on Linked Datasets.
For more information converting or consolidating values in a workbook visualization, see Currency in Datasets and Workbooks.
To learn how to use the workbook visualization filters, see the following:
Available Filters for Aggregate and Non-Aggregate Fields
When you work with filters, each field displays different options depending on the type of field that you select (numeric field, date field, and so on) and how you use the available fields in your workbook visualizations. In charts and pivot tables, you can use some fields in two different ways:
-
Use fields as aggregate fields – If you drag fields to the Measure section, the visualization displays results as an aggregate field. Therefore, results are grouped together to display a single summary result for several value entries.
-
Use fields as non-aggregate fields – If you drag fields to the Rows or Columns sections (for pivot tables) or the X-Axis and Color Stack sections (for charts), each aggregate field displays a result for each value entry.
Using fields as aggregate or non-aggregate values has an impact on how your workbook visualizations display results. For example, if you want to see the sales amount broken by sales rep, your workbook visualization can display results in different ways. By using the Amount (Net) field as an aggregate field, also known as a measure field, the visualization displays a single result for all the sales transactions of a particular sales rep, based on the aggregate function that you select. For example, if you select the sum function, the result returns a single value for all the sales transactions. In this case, the field displays 10,000 USD. If you use the Amount (Net) field as a non-aggregate field, the visualization displays a result for each sales transaction of the same sales rep. In this case, the visualization displays the following field results: transaction A (9,000 USD), transaction B (600 USD), and transaction C (400 USD).
Value-based Filters
Value-based filters enable you to filter the data displayed in your workbook visualizations using specific values from any field that has been added to the Layout panel or Table Viewer. In pivot tables, value-based filters are applied to every row and column in the table and removes those that do not contain the selected value. In charts, any columns, bars, or lines that do not contain the selected value are removed from the results.
To apply a value-based filter to a workbook visualization:
-
Click the Field Menu icon
next to any field in the Layout panel or Viewer and select Filter [Field Name]...The Filter window appears.
-
In the Filter window, select the field values and operator to use in the filter condition.
-
(Optional) In the search box, you can enter the value that you are searching.
-
Click OK.
The results are updated and the selected filter criteria is displayed above the Viewer.
Condition-based Filters
You can use condition-based filters in numeric fields to show results that match a specific numeric range. To do so, you can select the operator that you want to apply (greater than, equal to, and so on), and then enter the numeric value. For example, you can see the opportunity amounts broken by sales representative, and show only results with a won probability above 80%. To see these results, in your workbook visualization you can add a condition-based filter to the probability (%) field, and set the condition values to "greater" and "80%".
This option is available for percentage, integer and float fields that do not return currency values. If you want to apply this option to currency fields, customize your field using a TO_NUMBER formula function and changing the output type of the field. For more information about how to customize a currency field, see Advanced Sample Formula Fields.
To apply a condition-based filter to a workbook visualization:
-
Click the Field Menu icon
next to a field label and select Filter [Field Name]...The Filter window appears.
-
In the Filter window, select the operator to use in the filter condition, and then enter the numeric value.
-
Click OK.
The results are updated and the selected filter criteria is displayed above the Viewer.
Measure-based Filters
In pivot tables, measure-based filters enable you to filter out entire columns or rows using filter conditions that are applied to the totals displayed in the table, based on the measures defined for the table. In charts, any columns, bars, or lines with total values that do not meet the selected criteria are removed from the chart.
For example, assume you have set up the following pivot table and you only want to see customers with over thirty thousand dollars in total transactions:
To do so, you can apply a measure-based filter on the entity field of Entity by Total Amount (Transaction Currency) (Sum) greater 30,000.00.
You can also apply a measure-based filter to a dimension to display a certain top or bottom of your choice, for example, Entity by Total Amount (Transaction Currency) (Sum) TOP 10.
In both cases, a filter is applied to the Entity rows based on their total amount (transaction currency) totals.
If you are in a NetSuite account that has values in multiple currencies or a NetSuite OneWorld account with multiple subsidiaries, you must convert or consolidate these values before you can use them in a measure-based filter condition. For more information about currency conversion in SuiteAnalytics Workbook, see Currency in Datasets and Workbooks.
Measure-based filters are available in measure fields, also known as aggregate fields, and apply to charts and pivot tables only. You can apply measure-based filters from a dimension or a measure field.
To apply a measure-based filter to your pivot table or chart from a dimension field:
-
Click the Field Menu icon
next to a dimension field in the Layout panel or Viewer. -
Select Filter Top/Bottom.
The Filter window appears.
-
In the Filter window, select Top/Bottom (default option) or Greater/Less than, then select the measure and set the conditions for the filter.
-
Click Apply.
The table or chart automatically updates the results.
To apply a measure-based filter to your pivot table or chart from a measure field:
-
Click the Field Menu icon
next to a measure field in the Layout panel. In pivot tables, you can also access the filter by clicking the Field Menu icon next to the Grand Total field in the Viewer, or from any measure column if there are no dimension fields displayed in the columns section. -
Select Filter by and click a dimension.
The filter window appears.
-
In the Filter window, select Greater/Less than (default option) or Top/Bottom, then select the measure and set the conditions for the filter.
-
Click Apply.
The table or chart automatically updates the results.
Date Filters
Date filters enable you to filter your results based on a specific date, custom date, or dynamic date range that you choose. For example, you can select a dynamic date such as today, that update when the workbook is run. You can only apply one date filter per workbook visualization.
Date filters are available in date fields and apply to all workbook visualizations (charts, pivot tables, and table views).
To apply a date filter to a workbook visualization:
-
Click the Field Menu icon
in the Dataset Panel or Layout Panel next to a date field and select Filter Date> Advanced... -
In the Filter Date (Advanced...) window, use the Specific Dates tab to enter a custom date or the Date Ranges tab to select a dynamic date. Use the Values tab to select specific dates from the underlying dataset query results.
-
Click Apply.
The workbook visualization automatically updates the results.
The results are updated and the selected filter criteria is displayed above the Viewer.