Custom Filter

Owned by Madeleine Nolan (Unlicensed)
Last updated: Jun 15, 2023 by Aadesh Srivastav (Unlicensed)
4 min read

Creating and Editing a Widget Formula

The Data Browser enables you to use formulas to determine the values and filters of a widget. A rich variety of functions is provided for your use in formulas.

To create or edit a formula:

  1. Open the formula editor in the Data Browser

    1. For a new widget, click Select Data, and then “fx”.

b. For an existing widget, click the edit formula button.

2. The Data Browser then changes to display the Formula Editor, which has the following tabs:

c. The Data Browser tab provides selectable fields from the data model.

d. The Functions tab lists the functions that can be selected for your formula. Hovering over a function in the list displays a tooltip that describes that function.

3. Compose the formula as follows:

e. From the Data Browser tab, select one or more fields.

f. From the Functions tab, select the required functions.

4. Click OK.

  • Please note the formula you create is only applicable for the following widget and cannot be saved in a data cube.

  • If you want to use the same formula for another widget you have to recreate the formula. everytime.

  • Only , Advance Analytics - Global Formulas allows you to save these custom formula to cube

  • Click here to know more about Global formulas.

"Measured Filters" - Creating Formulas Based on Criteria and Conditions

Formulas take into account specific criteria using measured values, which only perform a calculation when the values meet the set of criteria.

Filter the formula:

  1. Create formula

  2. Add the field (criteria) by which you want to filter the formula. In the example below, this is Machine Name. Right click the field and select Filter

  3. Filter the formula by listed items, text options, rankings, etc.

    1. List Filter - A simple example of Measured Value is the use of a list filter

 

In the above example, Good Production is filtered by Machine Names, of which only 3 machine names are selected. Right click Machine Names, then select filter to open the list filter options as seen above.

  1. Ranking Filter - A more sophisticated case is the use of a ranking filter, for example to track the contribution of best-performing machines to total good production. However, what constitutes a “best performance” may change over time. A measured value can be created which includes a condition that only shows the top 5 machines for any period of time. This simultaneously filters the data but also takes into account changes in what classifies as a top performance over time.

In the above example, Good Production is filter by Machine Name, of which only the top 5 machines as ranked by their good production are included.

Measured Values are a powerful feature to take into account business logic and quickly perform calculations only when a specific set of criteria is met.

Note: If your widget is filtered using measured values, then the measured value will override any other widget or dashboard filters you have for the same fields.

Calculating Contributions with ALL Function

The All() function returns the total amount for a dimension, and can be used for various use cases. In the following example, we will use the All function to calculate how much each Plant contributed towards the total Good Production.

 

Step 1: The second column above represents a formula that sums up the total good production for all plants and does not represent the breakdown per plant. The formula is as follows:

Step 2: We can now use the above formula in another formula to calculate the contribution, like this:

 

The result is the last column above (plus formatting the results as percentages).