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 filtered 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

...

¡        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.

¡        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.

¡        You now have the following options:

-         If the condition lines below are to be evaluated independent of one another for all of the characteristics in the drilldown and you want to use the same condition for multiple characteristics of the query, select Evaluate Conditions Below for All Characteristics in the Drilldown Independently.

This setting allows you to apply a condition in a very general way. Depending on which characteristics you use in the drilldown, you will obtain varying results.

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

You can find more information under Ranked List Condition: Top 5 Products and Ranked List Condition for All Characteristics in Drilldown Independent: Top 3 Products and Top 3 Sales Channels.

¡        If you want to evaluate the condition for certain drilldowns of defined characteristics or characteristic combinations, in the field Evaluate the Conditions Below for Single characteristics or Combinations of Characteristics. You can then make entries in the list of characteristics used in the queries in rows, columns, or free characteristics.

                                                  i.       If you only want to evaluate the condition when a certain characteristic is present in the drilldown, select this characteristic.

                                                ii.       If you want to evaluate the condition for characteristic combinations, select multiple characteristics.  You can find more information under Ranked List Condition for Characteristic Combination: Top 3 Combinations of Product and Sales Channel.

¡        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:

                             b.      Select the variable entry. The formula variables that are available appear in the dropdown box.

                             c.      Select a variable.

§         You can change the variable using  Change Variable. The Variable Editor dialog box appears. You can find more information under Changing Variables in the Variable Editor.

§         If you want to create a new formula variable, select  New Variable. The Variables Wizard dialog box appears. You can find more information under Defining Variables with the Variables 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 Ranked List Condition with Two Condition Rows: Top 2 Products and Bottom 2 Products.

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

¡        Choose Save.

Changing a Condition

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 a Condition

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