The global filter spreadsheet controls what you see in the global filter menu and in the project tree. This is where you enter the axis (or axes) and element names that are displayed in the filter. These filters can then be used for analysis and reporting.
In this article
1. Global Filter Spreadsheet
Using an Excel spreadsheet, you can specify the items you want to filter the project by, and the items you want to see in the project. You don't need to list each and every single item, you only need to identify the exceptions. In other words, you need to include the items you want to control. Any items that don't need to be filtered, don't need to be added in the exceptions file.
If a site level global filter is used across multiple projects, all filters are stored in the same file.
If project level global filters are used, each file contains the filters for the specific project.
When creating your global filter spreadsheet, it is important to consider two questions:
Site Level vs. Project Level Global Filter Spreadsheet
Site Level Global Filters
Using a site level global filter can be beneficial if many filters are the same across multiple projects. Maintaining one spreadsheet can aid consistency and make it easier to update any requirements.
Project Level Global Filters
Using a project level global filter allows you to maintain filters that are specific to one project. This means that if there are many different filters across different projects, there is no confusion about what filters belong to each project.
Create an Excel Spreadsheet
The same format is used for both site and project level global filter spreadsheets. If there is no global filter spreadsheet for your project, you will need to create a new one to upload to your site or the project.
-
Create a new spreadsheet
- If you are creating a site level global filter, save it with the name globalfilter.xlsx. The file name is not case sensitive.
- If you are creating a project level global filter, save it with the name projectfilter.xlsx. The file name is not case sensitive.
-
Add column headers:
- In the first three column headers (A to C), enter the following: Header, Axis, and Element. These are the columns that control what you see in the Project Tree.
- In column D, enter the global filter category. Columns D onwards control what you want to filer by. The entered category displays in the global filter menu. You may have one or multiple Global filter categories, with each additional filter option in a new column.
-
Enter the exceptions: Once the headers are in place you can add the items required for filtering.
- Columns A, B, & C: What do you want to see in the project?
- Column D onwards: What do you want to filter by?
Labels entered in the global filter spreadsheet must match the item labels in your project. If you change any item labels, update the global filter file accordingly.
Upload the Global Filter Spreadsheet
Once you have completed your global filter spreadsheet, you need to upload it into Harmoni. There are two locations where you can store your global filter spreadsheet:
- At the site level - learn how to upload the site level spreadsheet.
- At the project level - learn how to upload the project level spreadsheet.
Project Level Global Filters Take Precedence
If a site level spreadsheet is included on a site and a project level spreadsheet is also loaded, the project uses the filters in this project level spreadsheet. If you then remove a project level spreadsheet, the project will use the site level filters if the labels are applicable.
2. Global Filter Examples
This section provides examples on adding information to your spreadsheet to create global filters.
Exceptions are only necessary for items that require specific filtering. You can define exceptions at the Header, Axis, or Element level. Data appears in the project tree when the value in Column D (Global Filter Option) matches the exceptions defined in Columns A, B, and C.
In the following examples, we will filter by the axis Country of Origin and so, we will add the axis name in the first row of Column D and the element names in the rows where the exceptions are added.
a. Header Level Exceptions
If an exception is set at the header level, everything under that header is displayed when the global filter is set to the option(s) mentioned in the global filter category column.
- When specifying an exception at the header level, no entry is required in the axis or element fields.
- If applying a global filter to a grid, the grid name must be specified in the Header field.
Examples:
- The CONSUMPTION section (header) must only be available when the Country of Origin is USA.
- Filter by: Country of Origin-USA
- See in the project tree: Consumption Grid
- The Brand Image Grid (header) and axes within the Grid must be available only when the Country of Origin is NZ.
- Filter by: Country of Origin-NZ
- See in the project tree: Brand Image Grid
Header level exceptions are also used when adding grids to a global filter spreadsheet. To learn more about filtering grids, review the Global Filters - Filtering Grids article.
b. Axis Level Exceptions
If an exception is set at the axis level, the axis, and every element in it, is only displayed when the global filter is set to the option(s) mentioned in the global filter category field.
- When specifying an exception to an axis, no entry is required in the header or element fields.
- If applying a global filter to an axis within a grid, the grid name must be specified in the header field and the axis name in the axis field.
Example:
- The axis, Region - NZ, has been added so that it is available only when the Country of Origin is NZ.
- Filter by: Country of Origin-NZ
- See in the project tree: Region - NZ
c. Element Level Exceptions
- If an exception is set at the element level, that element is only displayed when the global filter is set to the option(s) mentioned in the global filter category field.
- If an item is required for multiple options of a category, then the same Header/Axis/Element combination must appear on multiple rows, one for each category option it needs to appear in.
Example:
- For the axis Favorite Ice Cream Brand, certain brands (elements) must be available only when Country of Origin is NZ, USA or UK. Brands not included in this list will be displayed for all markets.
- Filter by: Country of Origin - Selected country
- See in the project tree: Favorite Ice Cream Brands from selected country
- Country elements (NZ, UK or USA) are only available in the project tree when the Country of Origin is the relevant market.
- Filter by: Country of Origin - Selected country
- See in the project tree: Selected country in any axis with the same elements as Country of Origin.
d. Grid Exceptions
When adding exceptions to the spreadsheet to allow filtering of different axes and elements within a grid, the two levels of filtering (axes and elements) need to be applied separately.
To filter the elements within a grid, the grid name should be added to the header (column A), and the element name to the element field (column C) in the same row as the applicable global filter label. The axis field should be left blank.
To filter the axes within a grid, the grid name should be added to the header (column A), and the axis name to the axis field (column B) in the same row as the applicable global filter label. The element field should be left blank.
In this way, you can control which axes display with which elements within a grid.
e. Global Filters and Constructed Nets
When you create a Net that includes all possible brands across multiple markets, and a global filter is selected for a specific market, it will not automatically filter out the brands that do not belong to the market. The reason for this is because the global filter is driven by label, and Harmoni does not infer any relationship between labels. The counts for a constructed Net element, e.g., Water Brands NET, that is not considered in the global filter spreadsheet are not filtered by the global filter.
If you need to filter specific brands for a market using a global filter, you need to create a construction specifically for the global filter selection that does not include the unrelated brands. Each Net would therefore need to be constructed for each global filter selection. In your project tree you can construct several Nets., e.g. 'Construction NET Market 1', 'Construction NET Market 2' and include these Nets in your global filter spreadsheet.
Where to from here?
Learn more about Global Filters.
- Global Filters - Upload the Global Filter Spreadsheet
- Global Filters - Turn off Global Filters when Editing a Project
- Analyzing Data with Global Filters
Useful Resources
Example of global filter spreadsheet