Logical Functions are useful when using functions for logical comparisons.  Situations such as testing two data points for equal values, testing data points for the greater value, and for other logical needs such as "and" and "or".  In this section, you will find helpful information on each of the available Logical Functions.

Figure 1:  The Logical Functions available are displayed in the Functions dialog.

 

Usage

To insert any of the available Logical Functions, select Functions in the Side Menu to access the Functions dialog.  Here you will find the available logical functions in  the Logical group.  Once a function is selected, a Function Object will be inserted into the Design Surface.  Data mapping can be completed by using the Data Connectors to create connections specifying what data will be used as input for the function and where the result will be used.  Below, each available function is highlighted to provide useful examples.

The Not Functions is used for situations where we want to find the negation of a input Boolean parameter.  An example of such usage would be if a database contained information about employees and their work hours.  In a monthly report, we need to list all of the logged departments that employees worked for with the exception of the Sales Department hours.

Logically, this example would list as "find all of the work history records where the department does not equal sales".  In the BI Data Analyst, we will need to use a diagram that tests if two values are equal, then proceeds to find the negation of this value using the Not Function.  For this, in Diagram 2, you can notice that the equals function is used to first test for department values equal to sales.  The result is used as a the input parameter for the Not Function, and the result will be used as the input parameter for the Filter function.  This will filter the data set to only provide records where the department is Not equal to Sales.  See the help section on the Not Equal Function for a more ideal approach to addressing this example scenario.

Figure 2:  The Not Function is used to find the negation of the Equals function result.

Figure 3:  The output preview screen displays the result of the Not Function showing all departments not equal to Sales.

The Equal Function is used to test if two values are equivalent.  This function can be useful for comparing two or more data fields for equality.  The result of this function will be a Boolean value of true or false.  An example of this function's usage is highlighted also in the example for Not Functions.  In this example, we test an input field from a database table to see if it equals a constant given the value "Sales".  This function will test each Department field for the value "Sales" and return true or false which will be used by another function.  You should be aware that by right clicking the Equal Function in the Design Surface, you can access the Application Bar to add additional input parameters to be used by your function.

Figure 4:  The Equal Function is used to test two input parameters for equality.

You can use the Not Equal function to return the value of true if two input parameters are not equal to each other.  Let's take the same scenario as described in using the Not Function.  A database contains information about employees and their work hours.  In a monthly report, we need to list all of the logged departments that employees worked for with the exception of the Sales Department hours.  We can use the Not Equal function to test for all department values that are not equal to the string "Sales".  Here, we will insert a Not Equal function, specify the Department field as our first parameter and the constant "Sales" as the second.  The result will be used to as our Filter function input parameter (a Boolean value).

Figure 5:  The Not Equal function is used to test two values and return true if not equal.  

Figure 6:  The output reveals all department records with department field Not Equal to Sales.

The Greater or Equal function is used in situations where you wish to test for values that are greater than or equal to another specified value.  An example of this function's usage would be returning only records from an employee work history table where the hours worked is greater than 1000.  In this example, we'll use a Greater or Equal function to test the Hours Worked field with a constant set to 1000.  Notice how Data Connections are made to map the input data points to as both input parameters in the function.  The result is mapped to be used as the input parameter in the Filter Function.

Figure 7:  The Greater or Equal Function is used to filter a data set to only hours above a specified constant value.  

Figure 8:  The output preview reveals only records with hours worked greater than or equal to 1000.

The Greater function is used to compare the first input parameter with a second and returns the value true if the first parameter (op1) is greater than the second (op2).  For this example, we will use the scenario where we would like to obtain only records from a database table where the work hours  are greater than 1000 (but not including 1000). This is where the Greater Function will prove useful.  We will simply need to insert into the Design Surface, then use both the Hours Worked field and numerical constant (1000) as input parameters op1 and op2.  We will use the result as an input parameter for our Filter function.

Figure 9:  The Greater Function is used to test for if the hours worked are greater than 1000.

Figure 10:  The output preview displays the records when the hours worked field is greater than 1000.

The Less or Equal function is used in situations where you wish to test for values that are less than or equal to another specified value.  An example of this function's usage would be returning only records from an employee work history table where the hours worked is less than 1000.  In this example, we'll use a Less or Equal function to test the Hours Worked field with a constant set to 1000.  Notice how Data Connections are made to map the input data points as both input parameters in the function.  The result is mapped to be used as the input parameter in the Filter Function.

Figure 11:  The Less or Equal function is used to find records with hours worked less than or equal to 1000.

Figure 12:  The output preview displays only records with hours worked less than or equal to 1000.

 

   

The Less function is used to compare the first input parameter with a second and returns the value true if the first parameter (op1) is less than the second (op2).  For this example, we will use the scenario where we would like to obtain only records from a database table where the work hours  are less than 1000 (but not including 1000). This is where the Less Function will prove useful.  We will simply need to insert into the Design Surface, then use both the Hours Worked field and numerical constant (1000) as input parameters op1 and op2.  We will use the result as an input parameter for our Filter function.

Figure 13:  The Less function is used to return filter output to be only using records with hours worked less than 1000.

Figure 14:  The output preview displays the records only with hours worked values less than 1000.

The And Function is very useful for combining logical operations specifying when certain logical tests must be satisfied.  This function take Boolean values as parameters, and you can right click on the Function Object to access the Application Bar where an option resides to Add Parameters.  In this example, we'll use the And Function to set up two logical tests that must be satisfied in order to obtain our data.  We want to filter our data set to retrieve values only when the Hours Worked field is less than 1000 And when the Department field is equal to "Accounting".  You can see in Figure 15, that the result of each function is used as the Boolean parameter in the And Function.  The And Function will ensure both of these Boolean results must be true in order to return a true value to the Filter function.

Figure 15:  The And Function is used to combine two logical tests for filtering.

Figure 16:  The output preview shows results where both the department is Accounting and the hours worked are less than 1000.

The Or Function is useful to combine logical tests where the result will be true if either of the specified Boolean input parameters are true.  To demonstrate this usage, we've used a scenario where we would like to filter our employee work history table to only retrieve data where the Department field is equal to "Accounting" Or when the Department field is equal to "Sales".  To accomplish this goal, we set up two Equal Functions to test for these values, and pass the results to the Or Function using their Boolean values as input parameters op1 and op2.  If either of the logical tests is true, the record will be used in the output data source.

Figure 17:  The Or Function is used to test results of two equal functions.

Figure 18:  The results of the Or Function reveal filtered records for when department is equal to Accounting and Sales.