Filter data cube using complex logical expression

Contents[Hide]

1. Overview 

This article shows you how to filter the data cube with complex logical expressions.

In cases of logical expressions that use a combination of AND and OR operators, the Filter transform is not adequate to filter the data needed.

In this case, we need to use the combination of the Calculated Element transform and the Filter transform

2. Editing expression in Calculated Element transform

Once the table has been placed in the Data Cube Designer, place the Calculated Element transform.

Calculated Element Transform
Calculated Element Transform

Configure the transform, and click the Add icon under Calculated elements.

Adding calculated element
Adding calculated element

Name the calculated element, and set the data type of the element to Boolean. Next, click on the "Edit expression on editor". This will open the Script Editor.

Edit expression for boolean
Edit expression for boolean

In the Script Editor pane, place the conditions that you need to want to filter out in the data cube. Make sure that it meets the criteria you want to accomplish, and return "true".

For example, we want to return values that have the following condition: (COL_A > 5 AND COL_B > 5000) OR (COL_A = 1 AND COL_C > 0.01)

In this case, we will use a script shown below:

if (($TerritoryID$ > 5 && $SalesYTD$ > 5000) || ($TerritoryID$ == 1 && $CommissionPct$ > 0.01)){
  return true;
}

 

Script Editor with the expression
Script Editor with the expression

For the list of columns that you could use to create the expression, you could check on the Placeholders section in the Calculated Element screen.

Available placeholders
Available placeholders

Click "Save", and it will refresh the data cube to show the results. It will have another column in the data cube to display the results of the transform.

Result of calculated element
Result of calculated element

3. Placing the Filter

After placing the Calculated Element transform, add the Filter transform, and configure it.

Filter transform
Filter transform

In Filter, click on Edit item next to the created Calculated Element.

Edit Calculated Element column in Filter
Edit Calculated Element column in Filter

Select the Element Operator "Equals", and place a checkmark under Boolean Value.

Boolean value
Boolean value

Click OK, and click on Process Results. In the Data Preview, you will see the filtered results.

Result of filter
Result of filter

On the Data Output Elements, click 'X' on the column with the Calculated Element. This will hide the boolean transform.

Output elements
Output elements

See also

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri