Learn about the advanced search rule types

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. 

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:

Open

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.

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.

Example: Identify the sales reps who earned sales revenue of less than $100,000 in the financial year to date.

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.

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 (plus) 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 4^th December 2021 and end date as 5^th December 2021. Next, copy the variable two times and edit the date of variable b to start on 11^th December 2021 and end on 12^th December 2021, and variable c to start on 18^th December 2021 and end on 19^th 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.