Skip to main content

Celonis Product Documentation

Understanding the IM Knowledge Model KPI code
Explaining the Base KPI

The Base KPI is a way of using a KPI as a variable. This allows the same KPI to be referenced multiple times in the IM Apps and requires only the time frame parameters to be amended. This simplifies maintenance as all KPIs that reference the Base KPI will be updated automatically if the Base KPI is amended.

Base KPI
Example: Base KPI
 - id: NET_SALES_VALUE_SINGLE_SUM_VARIABLE_TIMEFRAME_BASE   
    displayName: Net Sales   
    pql: |     
        SUM(       
            CASE          
                WHEN ${SALES_TIMEFRAME_VARIABLE_BASE} <= {p1}         
                AND ${SALES_TIMEFRAME_VARIABLE_BASE} >= {p2}         
                THEN KPI(VBAP_CURRENCY_CONVERSION)         
                ELSE 0.0       
            END     
           )   
    unit: " ${{to-currency}}"   
    format: .3s   
    parameters:     
        - id: p1       
            displayName: lower limit       
            description: Furthest time in the past, eg. 12 would represent 12 months into the past       
            defaultValue: 12     
        - id: p2       
            displayName: upper limit       
            description: Closest time in the past, eg. 1 would represent 1 month in the past       
            defaultValue: 1   
    desiredDirection: NONE   
    priority: false

Item

Description

NET_SALES_VALUE_SINGLE_SUM_VARIABLE_TIMEFRAME_BASE

The Base KPI; this is often denoted by adding the suffix _BASE to a KPI name.

SALES_TIMEFRAME_VARIABLE_BASE

Returns the months between the Sales Order Goods Issue date {p1} and the Last Load Date {p2}.

Parameters {p1} and {p2} can be amended in your app according to your use case.

VBAP_CURRENCY_CONVERSION

A previously-defined KPI that stores the Sales Order Item value:

Converting from the from-currency to the to-currency
Example: Converting from the from-currency to the to-currency

Note

This example shows the output of the net sales value converted into the target currency. There are no other operators like aggregations at play. If used in a table with the Sales Order Item’s primary keys, you would simply see the net sales order item value for that item.

This same approach applies to the other currency conversions that are performed throughout the application, such as for inventory value on hand, material consumptions and replenishments.

 - id: VBAP_CURRENCY_CONVERSION   
   displayName: Inventory on Hand (Val)   
   pql: |     
        COALESCE(       
            CURRENCY_CONVERT_SAP (         
                '${{to-currency}}' -- currency you would like to convert to (updated automatically by the package variable)         
                ,'${{currency-conversion-type}}' -- currency conversion type (updated automatically by package variable)         
                ,"VBAP"."MANDT" -- Client column for VBAP         
                ,'${{from-currency}}' -- currency you are converting from (updated automatically by the package variable)          
                ,"VBAP"."TS_ERDAT" -- date that you wish to use for the conversions         
                ,"VBAP"."NETWR_CONVERTED" -- Net value converted field as transformed in the Data Transformation       
           )     
          , 0.0)   
   unit: " ${{to-currency}}"   
   format: .3s

Referencing the Base KPI
Example: Referencing the Base KPI
 - id: NET_SALES_VALUE_SINGLE_SUM_12M_BASE   
   displayName: Net Sales (Val, 1y)   
   description: This kpi sums up the total sales value over the last year across all materials and plants. It takes the value from the NETWR field in the Sales order item table (VBAP) and converts it into the target currency.   
   pql: |     
        KPI(       
            NET_SALES_VALUE_SINGLE_SUM_VARIABLE_TIMEFRAME_BASE       
            ,12       
            ,1     
        )   
    unit: " ${{to-currency}}"   
    format: .3s   
    parameters: []   
    desiredDirection: INCREASE   
    priority: false

Item

Description

NET_SALES_VALUE_SINGLE_SUM_VARIABLE_TIMEFRAME_BASE

Referenced inside another formula or KPI by wrapping KPI() around the ID.

Parameters

Upper limit of 12 ensures the SUMs lower limit goes as far back as 12 full months into the past).

Lower limit of 1 ensures the SUM ends upper limit goes back 1 month into the past, ie. the last closed month.

Converting the from-currency to the to-currency
Example: Converting from the from-currency to the to-currency

Note

This example shows the output of the net sales value converted into the target currency. There are no other operators like aggregations at play. If used in a table with the Sales Order Item’s primary keys, you would simply see the net sales order item value for that item.

This same approach applies to the other currency conversions that are performed throughout the application, such as for inventory value on hand, material consumptions and replenishments.

 - id: VBAP_CURRENCY_CONVERSION   
   displayName: Inventory on Hand (Val)   
   pql: |     
        COALESCE(       
            CURRENCY_CONVERT_SAP (         
                '${{to-currency}}' -- currency you would like to convert to (updated automatically by the package variable)         
                ,'${{currency-conversion-type}}' -- currency conversion type (updated automatically by package variable)         
                ,"VBAP"."MANDT" -- Client column for VBAP         
                ,'${{from-currency}}' -- currency you are converting from (updated automatically by the package variable)          
                ,"VBAP"."TS_ERDAT" -- date that you wish to use for the conversions         
                ,"VBAP"."NETWR_CONVERTED" -- Net value converted field as transformed in the Data Transformation       
           )     
          , 0.0)   
   unit: " ${{to-currency}}"   
   format: .3s