Filtering and Pivoting Data
In Analytics, you can arrange and limit the data you see based on the criteria you specify. For example, you might want to see results from the last three months, for a certain provider, or for work orders across regions. You can do using the following:
- Filters restrict the data to items of interest
- Pivots (for Dimensions only) become a data column where the information easier to consume visually
- Row and Column Limits to control the number of data rows your visualization displays. We briefly discussed Row Limits on Running the Data and Viewing the Visualization. Here, we will dig deeper by including Column Limits into the discussion.
Filtering Data Overview
Use filters to restrict the data you are viewing to items of interest: date ranges, providers, locations, labels, weather type, asset, and more. You can use any data as a filter, and you can combine filters to truly tell your story.
Below are the types of filters available:
- Basic Filters: This filter is the most commonly used when building data and visualizations in the Explore.
- Advanced Matches: A more complex filter, it helps you specify more advanced field conditions such as intricate text searches or a date range that starts in the past and goes on for a certain number of days. This filter uses strings, boolean expressions, GPS location expressions, and numerical expressions.
- Custom Filters: Use custom calculations and a mixture of operands to create your own dynamic filter.
Using Basic Filters
This filter is the easiest to apply as you are building your reports and visualizations. There are several different ways to add a basic Filter in the Explore:
- Under the Dimensions and Filters list, click the Filter button. With this method, you can filter your data by any attribute even if it is not a part of your data set.
- In your data set, click a data point under a Dimension, and then select Filter On <data point>.
- In your data, choose the column header of a Dimension or Measure, and then click the gear icon
.
In all cases, the enabled filter appears on the top of the Explore. Click Run to refresh the data and visualization with the enabled Filters.
Using More Than One Criteria for the Same Filter / Filter Operands
By clicking the plus + icon to the right of a filter, you can add multiple criteria for the same filter. Analytics adjusts the operand of the subsequent filters to either OR or AND based on your selection. Let's look at two examples on how this works.
Example: How Operands Change Based on Criteria Selected
Say that there are 20 locations, all numbered 000 - 020. We are using the Location Number filter in this example.
Example 1: We want to filter all location numbers that start with "00", yet exclude location 005. The first criteria would be starts with 00, and the second criteria would be is not equal to 005. Analytics changes the operand to AND automatically.
Example 2: We want to filter all location numbers that either start with "00" or are blank. The first criteria would be starts with 00 and the second would be is blank. Analytics changes the operand to OR in this case.
Both examples are illustrated below.
Using Advanced Match Filters
There are two advanced filters: Matches (Advanced) and Matches a User Attribute.
Adding an advanced match filter starts the same way as a basic filter — choose from the list, click a data point, or click the gear icon. From there, select Matches (Advanced) or Matches a User Attribute from the drop-down menu of the selected filter.
With Matches (Advanced), you can enter dynamic filter expressions using, for example, strings, boolean expressions, GPS location expressions, and numerical expressions. You can use special characters (such as commas and quotes) while using advanced matches filters, but you have to use the carat symbol ( ^ ) before the special character. For example, to filter on Santa Cruz, CA you would enter Santa Cruz ^, CA. Notice the carat in front of the comma ( ^ , ). See Types of Filter Expressions for a comprehensive list.
With Matches a User Attribute, you can filter data based on your user information, such as your name, email, or language.
Custom Filters
Custom filters let you create filters with custom conditions that might not be available with the other, simpler filter types. You write the fields, constants, functions, and operators that express the filtering that you want.
On the top of the Explore with the other basic and/or advanced filters, click Custom Filter to display an editor where you to build your expression.
You can only use Dimensions in your expression. You cannot refer to Measures in a custom filter, or use any function/expression that relies on query results.
Your expression is evaluated as True or False conditions. When you run the query, Analytics will return the rows for when the condition is True.
Although you may use as many fields, functions, and operators as your logic requires, note that the more complex your condition, the longer it will take to return query times.
Next Section: Customizing the Visualization
So you have run and refined your data, and now you have it just about where you want it. Now, let's make your chart look good.
