Defining Conditions 

Use

You can formulate conditions in the query designer to make data analysis more efficient. In the results area of the query, the data is restricted according to the conditions so that only the part of the results area that you are interested in is displayed.

You can find additional information about the scope of functions for conditions under Conditions.

Procedure

...

       1.      Choose New Condition. (You can find this menu entry in the Query Designer toolbar under  to the right of  Condition). The Define Condition dialog box appears.

       2.      Specify a name for the condition in the Description field.

If you only want to define the condition and not execute it actively in the query, remove the check from the active, which was set by default.

       3.      You now have the following options:

-         Choose in the field Evaluate conditions displayed below for all characteristics in the drilldown independently if you want to evaluate the lower condition rows separately for all the characteristics in the drilldown.

Characteristics that are already used in an active condition for characteristic combinations are excluded from these.

The condition row is Sales Top Five.

If the characteristic Customer is the only one drilled down in the query, the top 5 customers in terms of sales are displayed on the basis of this condition. If the characteristic Product is the only one drilled down in the query, the top five selling products are displayed. If Customer and Product are in the drilldown, the top five customers with regard to sales, as well as the top five products with regard to sales, are displayed.

-         Choose in the field Evaluate the conditions displayed below for single characteristics or combinations of characteristics if you only want to evaluate for certain drilldowns of defined characteristics or characteristic combinations. You can then make entries in the list of characteristics used in the queries in rows, columns, or free characteristics.

a.       If you only want to evaluate the condition if it is in the drilldown, select it with a check.

The condition row is Sales Top Five.

You have selected Customer as the only characteristic. The condition is only evaluated if Customer is in the drilldown. Only the top five customers with their sales are displayed.

b.       Select several characteristics with a check, if you want to evaluate the condition for characteristic combinations.

The condition row is Sales Top Five.

You have selected the characteristics Customer and Product with a check. The condition is only evaluated if Customer and Product are in the drilldown at the same time. The option All characteristics in the drilldown independently displays the top five customer-product combinations with sales, meaning only five rows instead of 25 (see above).

If you have defined several conditions for a query, there may be conflicting conditions that refer to the same characteristics. In this case, certain rules of priority apply. Generally the most specific condition applies when there are conflicts.

One condition defines the top five sales for the characteristic Customer, and a second condition defines the bottom three sales in the customer-product combination.

If only the characteristic Customer is in the drilldown, only the first condition applies. So, only the condition top 5 sales is used for the characteristic Customer.

If Customer and Product are in the drilldown at the same time, you can use both conditions according to their definitions. In this case, the second condition applies since it is more specific due to its reference to the characteristic combination.

       4.      In the field Display all values for which at least one of the following conditions applies, enter one or more condition rows into the list. See Conditions/Exceptions: Add, Change, Delete Rows

A condition row is always derived from the determination of an element for every structure used in the query (usually one or two structures), an operator, and a value that matches the operator.

You can choose from the following operators for threshold conditions:

-         Is equal to

-         Is not equal to

-         Is less than

-         Is greater than

-         Is less than or equal to

-         Is greater than or equal to

-         Is between (is in the interval)

-         Is not between (is not in the interval)

These operators demand a floating point number as a value. For the operators is in interval and is not in interval, you must enter a value area, that is, a lower and an upper threshold value.

You can choose from the following operators for ranked list functions:

-         Top N

-         Bottom N

-         Top percent

-         Bottom percent

-         Top sum

-         Bottom sum

You can find additional information about using operators for range lists under Conditions.

For Top/Bottom N you must enter a whole, positive number as the value. For Top/Bottom percent enter a number between 0 and 100. For Top/Bottom sum you can also enter a floating point number (for example, 15.7).

The format depends on the country setting in the operative system.

The following convention makes it easier to handle numbers with many zeros:

An apostrophe after the number means multiplied by one thousand, a quotation mark means multiplied by one million, and three apostrophe marks means multiplied by one billion, for example 1.5” = 1.5 mil = 1,500,000.

You can also use formula variables as the reference value of the condition.

Select Variables Entry.

The available formula variables appear in a dropdown box. Select a variable. You can change the variable using  . The Variables Editor dialog box appears. For more information, see Changing Variables in the Variable Editor.

Choose  if you want to create a new formula variable. The Variables Wizard dialog box appears. For more information, see Defining Variables with the Variable Wizard.

When you execute the query, a Variables dialog box appears, in which you can choose the value limit of the condition.

A group of condition rows is logically linked with OR. This means that at least one of the condition rows has to be fulfilled for the entire condition to be true. See also Condition: Top 2 Products and Bottom 2 Products.

       5.      Choose OK. This takes you back to the query definition.

       6.      Choose Save.

Changing Conditions

1.       Choose Change condition. (You can find this menu entry in the Query Designer toolbar under  to the right of  Condition). The dialog box for conditions that have already been defined appears.

2.       Choose the condition you want to use, and then choose OK. You get to the dialog box Defining conditions, where you make any changes.

3.       In the Query Designer, choose Save.

Deleting Conditions

1.       Choose Condition. You get to the selection window for the conditions that have already been defined.

2.       Choose the condition you want, and then choose Delete.

3.       In the Query Designer, choose Save.

Result

You have defined or changed a condition for a query. In the status bar of the Query Designer, the   symbol in the lower right-hand corner shows you that one or more conditions have been defined for this query.

Execute the query. Only those objects are displayed which fulfill the defined conditions.

You can display the condition descriptions in the query view and activate or deactivate them there. See Using Conditions.

See also:

 

Background Information on Conditions