• All Files
Develop > Customize KPIs, Metrics, Pages, and More > Create the Dashboard Contents in the Studio > KPI or Metric Formula

KPI or Metric Formula

You can change the existing formula of a KPI or Metric to customize it to the end-user's needs. You can also create a formula to calculate the value of the KPI or Metric .

In addition you can restrict the range of the formula by selecting, in the filter, specific values for the variables used in the formula. For details, see KPI or Metric Filter.

To learn about Shared KPIs, see Shared KPIs.

ClosedTo access:

Click Open Formula Builder in the Calculation Details tab in the Properties pane of a KPI or Metric .

ClosedKPI and Metric Formula and Filter, Threshold, Value, Trend, and Score

For details about KPI and Metric formulas, filter, values, decimal precision, thresholds, status, score, and trend, see KPI and Metric Formulas, Filter, Threshold, Value, Trend, Score, and Decimal Precision.

ClosedImportant Information

  • The KPI or Metric Formula language is not case-sensitive.
  • Field values are case-sensitive.
  • Formulas include single quotes only for entity field values and string primitives (numbers, characters between double-quotes, and dates).

  • During calculation, formulas are converted into SQL queries that run on the database.

You can use additional variables and entities by creating business contexts using Context Designer. For details, see Semantic Layer - Context Designer

ClosedKPI Breakdown Formula Limitation

When you configure the formula of a KPI for which you will set up Breakdowns, make sure that you do not use multiple entities.

Example of a correct formula, the formula uses the same entity for the numerator and denominator

PERCENTAGE_MATH(SUM(CostAmount,CostExpenseType = 'CAPEX' and PERIOD_ENTITY=Period),SUM(CostAmount,CostExpenseType = 'OPEX' and PERIOD_ENTITY=Period),100)

Example of an incorrect formula; the formula uses two different entities for the numerator and denominator and that might cause the Breakdown calculations to be incorrect.

PERCENTAGE_MATH(SUM(CostAmount,CostExpenseType = 'CAPEX' and PERIOD_ENTITY=Period),SUM(Cost,CostExpenseType = 'OPEX' and PERIOD_ENTITY=Period),100)

ClosedImpact of changes to configurations in the Studio on the display in Dashboard and the EXPLORER tab

Changes in the STUDIO have impacts on the display in Dashboard and EXPLORER. For details, see Synchronization of Changes in the Studio with Display in the Dashboard and the EXPLORER tab.

ClosedLink between Contexts and KPI or Metric Formulas

When you create a Context, you select tables, create links between the tables, and select the relevant columns in the tables to be part of the Context. For details, see Semantic Layer - Context Designer.

When you create a KPI or a Metric you assign them a Context in the Studio. The Context tables become variables that you can use when creating KPI or Metric formulas, and the table columns become the variable entities.

For example the % of Reopened Defects KPI has the following formula:

RATIO_MATH(COUNT_DISTINCT( DefectHist.Defect , DefectHist.ReopenFlag =1 And Defect.Status <> 'Closed') , COUNT( Defect ,Defect.DefectSummary<> 'INVALID' And Defect.DefectSummary<> 'UNKNOWN' ),0)*100

Its definition is:

The Business Context is ALM_Defect. Click Admin > Semantic Layer and open the ALM_Defect Context:

The contents of the Context including the ALM tables is displayed:

In the Entities area on the right, you can see the tables and the table columns that were selected to be part of the ALM_Defect Context itself.

It is recommended that the person who creates the KPI or Metric formula should be familiar with the ALM entities that are used in the ALM_Defect context:

The Defect_Hist table corresponds to one of the entities (DefectHist) that appears in the list of Variables that can be used when creating or modifying the formula of a KPI based on the ALM_Defect context. To access the KPI or Metric formula, click Studio, highlight the relevant KPI (% of Reopened Defects for example), click the Calculation Details tab, and then click Open Formula builder.

The variables you see in the formula builder correspond to the Entities in the Context Designer (above) and correspond to the <EntityName> in the syntax of the formulas (as explained in KPI or Metric Formula .)

When you expand a variable, the items you see below correspond to the fields (in the Context Designer) and correspond to the <FieldName> items in the formulas syntax (as explained in KPI or Metric Formula.)

ClosedUse Case - Configure a KPI or a Metric formula

In this section you will learn how to configure a KPI or a Metric formula.

  1. Click the Studio button (top right corner) to open the Studio.
  2. In the Active KPIs pane, select the KPI or Metric whose formula you want to modify.
  3. In the right pane, click the Calculation Details tab and click the Open the Formula Builder link.
  4. Remove the current formula and start creating your own formula.
  5. Click Validate to make sure that the syntax is correct.
  6. Click OK to close the dialog and click Save to save the changed formula.
  7. Use the Test KPI button to check if the result you are getting according to the configuration, meets your expectation. If not, modify it and check it again.

Example 

Let’s create a formula with the COUNT function (for this example we will use Incident Management as Business Context).

  1. From the Functions pane, double click the COUNT function or select the COUNT function and click the Add button, or select the COUNT function and drag it to the bottom area.

    The syntax of the formula appears above in the description area.

    The COUNT function returns the number of entities satisfying the given Filter Expression condition. For example, if you want to count the number of open incidents you need the following entities:

    • Entity to count is Incident
    • Filter expression is Incident.Status = ‘Open’

  2. Let’s add these entities to the COUNT function according to its structure:

    1. Click inside the formula brackets, go to the Variables pane, and locate the Incident entity (you can use the search field).

    2. Double-click the entity or select the entity and click the Add button.

      The Incident entity appears in the first section of the COUNT function.

  3. Move to the second section of the COUNT function, and locate the Status entity in the Variables pane.
  4. Add it to the formula.

  5. You can now add the condition operator equal and select the Open value from the list.

    The formula should be as follows: COUNT (Incident, Incident.Status=’Open’)

Movie: To display the relevant movie, open the ITBA application, click Help in the top right corner of the application main page and select the Movies option. Select the relevant movie. For details, see Access Movies.

ClosedUse-Case for IN_PERIOD

ClosedFormula Builder Dialog Box

Send Help Center feedback