This page gives information and examples for each type of advanced search rule. See the Perform an advanced search page to learn how to access the advanced search, create and apply the advanced search rules and more.
Each advanced search rule has two key elements:
Rule type (identified by purple boxes in image below) - There are five types of rules you can create for a selected entity: Entity, Property, Measure, Variance and Calculation. You can use these rules individually or combine multiple rules for a more detailed search.
Search type (identified by red boxes in image below) - For each rule type, several search types (operators) are available: Equal To (default), Not Equal To, Less Than, Greater Than, Like, Not Like, Between.
An entity is a row of data within a dimension, such as customer, sales rep, product, vendor, account, and so on.
The Entity rule is based on a selected entity (or multiple entities) and it returns the same result as the action of selecting and focusing on data in the grid. The Entity rule is the most basic of the advanced search rules, as you are not entering information, just making selections. For example, Brand = ACME, or Rep = Harry and Daisy. Therefore, the search type options are restricted to Equal To and Not Equal To. Typically, you use an Entity rule to supplement other rules; you would not use it in isolation.
If you make select and focus on data before starting your advanced search, the Entity rule is already populated for you, which is faster than manually creating it.
After adding the rule, start by selecting a search type. Next, click the dimension button to display an alphabetical list of all available entities within that dimension. You can filter the entity list by typing a keyword into the text box or browse the list of entities, then select the required entities.
Example: View data for all territories, except for the seven territories in Australia:
See the Example - Multiple rules page to learn how to use the Entity, Measure and Variance rules together, to locate declining customers who have spent a particular amount.
Watch the Advanced search by entity video to learn how to create an Entity rule.
A property is a type of information stored about a dimension. Properties display as columns in the grid when you select a dimension.
The properties that are available in the Properties rule depend on the dimension that you select. For example, the Product dimension might have the Code and Name properties only, whereas the Customer dimension might have Code, Name, Address, Sales Rep, Customer Type and more.
After adding the rule, click the default property button (usually this is Code) to view a list of the available properties and select the required property, then select the search type and enter a search term or value.
Example: Get a list of all customers whose zip (post) code is between 2000 and 2100.
Watch the Use advanced search to narrow customer properties video to learn how to add Property rules.
A measure is a type of numerical data, such as value, quantity, profit, margin.
The Measure rule allows you to create a rule based on the measures in your current database. This rule contains various settings you can use to define the measure, including the ability to select a period type and specify dates. As it includes period information, the Measures rule is more advanced than the Property and Entity rules.
After adding the rule, click the default measure (usually something like Sales Local Value) button to open the Measures window and proceed to define the measure. Then select the search type and enter a value.
A common mistake is to assume that a measure-based advanced search is automatically filtered by selections made either before entering advanced search or subsequently. For example, using a measure rule to find all customers with a sales value greater than $1m will return all customers with sales over that value. If you then focus on another dimension, such as Product Group and display the customers, it is quite probable that the list will include rows where the sales value is less than $1m. This is because the selection of customers based on their total sales is acting independently of the filtered display. If you want to only see the customers with total sales greater than $1m for a specific Product Group, you need to edit the rule and add the selection via the Selections tab. See Define the measure to learn how to do this.
Example: Identify the sales reps who earned sales revenue of less than $100,000 in the financial year to date.
See the Example - Multiple rules page to learn how to use the Entity, Measure and Variance rules together to locate declining customers who have spent a particular amount.
Watch the Create a 'low margin' alert video to learn how to add Measure rules then save the search results as an alert.
The Variance rule uses a simple algebra to make a comparison between two variables, ‘a' and 'b’. The Variance rule allows you to compare two different measures. For example, you might want to find sales reps who are performing below the budget level.
After adding the rule, click the measure button for variable a to open the Measures window and define the measure. Repeat for variable b. Next, create the rule to determine the difference between those variables. By default, the operation a - b (the value of measure a minus the value of measure b) applies but you can click the a - b button to switch to a % b (the value of measure a minus measure b expressed as a percentage of measure b). Then select the search type and enter a value.
Example 1: Identify sales reps who are performing better than budget
This basic example uses one Variance rule to identify sales reps whose sales were more than 10% over the budget.
Example 2: Identify declining customers
This more complex example uses three Variance rules with offset dates to identify customers whose sales have declined at least 5% per month for the last three months in a row, not including current month. Firstly, create one rule, using the a % b operation and the Less Than search type. In the Measure window, for both variables a and b, select the required stream and measure, and the Custom (Month) time unit and Offset option. Enter the start and end offset of -1 for variable a and -2 for variable b. Copy that rule two times, then edit each rule, so that the start and end offset in the second rule is -2 for variable a and -3 for variable b, and in the third rule, is -3 for variable a and -4 for variable b.
After applying the rule, apply a custom period to the grid to reflect the search results.
See the Example - Multiple rules page to learn how to use the Entity, Measure and Variance rules together to locate declining customers who have spent a particular amount.
The Calculation rule allows you to use basic mathematical operations to write your own rule based on a number of variables ('a',' b', 'c' and so on) that you define as measures or numerical properties.
After adding the rule, click the Add 
button > Measure to add a variable, then click the measure button to open the Measures window and define the measure. Repeat this step to add and define the other measures. Next, enter the relevant mathematical expression, in which you can use plus, minus, multiply, divide, brackets and numbers. The standard order of operations is followed, where multiplication and division operations are processed before addition and subtraction, regardless of position in the expression. You can use brackets to override that order. Lastly, select the search type and enter a value.
Example 1: Identify customers whose average profit for a period is more than a certain amount.
This example uses one Calculation rule with two variables to identify the customers whose average profit for January (variable ‘a') and February (variable 'b’) is more than $1000.
Example 2: Identify products that exceed the sales target
This example uses a Calculation rule with three variables to identify products that exceeded a promotional target. Suppose you are planning a new promotion and want to have a look at the results the promotions you ran this time last year. There were promotions on three separate weekends in December, in which you want to identify all products that exceeded the target of $10,000 in sales.
Add one variable (measure) and select the Stream > Sales and Measure > Value. On the Period tab, set the time unit to Day and set the start date as 4th December 2021 and end date as 5th December 2021. Next, copy the variable two times and edit the date of variable b to start on 11th December 2021 and end on 12th December 2021, and variable c to start on 18th December 2021 and end on 19th December 2021. Then, to add up the sales on all the promotional days, you enter the formula 'a+b+c' into the Expression box. Lastly, select the Greater Than search type drop and enter 10000.
Watch the Use advanced search to find decreasing monthly average video to learn how to add a Calculation rule.