Standard Functions
| Syntax | Details | Example |
|---|---|---|
| IF(intarg , arg2, arg3) | Return arg2 if intarg is considered true (not equal to zero), else return arg3. | CAPACITYOVERLOADS@PERLOCRES = IF(“CAPADEMANDUTILPCT@PERLOCRES”> 1,1,0) |
| ISNULL(arg1) | Return 1 (= true), if arg1 is set to null. | MARKETINGFORECASTQTY@PERPRODCUST = IF(ISNULL(MARKETINGFORECASTQTY@PERPRODCUST), SALESFORECASTQTY@PERPRODCUST, MARKETINGFORECASTQTY@PERPRODCUST) |
| CASE(arg1, default)CASE(arg1, cmp1, value1, cmp2, value2, …, default) | Return value1 if arg1 == cmp1, value2 if arg1 == cmp2, and so on, default if there is no match. | CASE(SELECTEDOPTION@PERPROD, 1, KF1@PERPROD, 2, KF2@PERRPROD,KF@PERPROD) |
| ABS(arg) | Returns arg, if arg is positive or zero, else –arg. | IF(ABS(SUPPLYREV@PERPRODFML – CONSENSUSDEMANDREV@PERPRODFML)/CONSENSUSDEMANDREV@PERPRODFML > 0.2,1,0) |
| ROUND(double, int) | ROUND(123.456, 0) = 123ROUND(123.456, 1) = 123.5ROUND(-123.456, 1) = -123.5ROUND(123.456, -1) = 120 | KF1@PERPRODLOCSRC = ROUND(KF@ PERPRODLOCSRC, 0) |
| ROUNDDOWN(double, int) | ROUNDDOWN(123.456, -1) = 120ROUNDDOWN(-123.456, -1) = -130 | KF1@PERPRODLOCSRC = ROUNDDOWN(KF@ PERPRODLOCSRC, 0) |
| FLOOR(double) | FLOOR(35.1) = 35 | KF1@PERPRODLOC = FLOOR(KF@ PERPRODLOC) |
| CEIL(double) | CEIL(35.1) = 36 | KF1@PERPRODLOC = CEIL(KF@ PERPRODLOC) |
| LTRIM(string)LTRIM(string,string) | Remove a whitespace prefix from a string. The whitespace characters may be specified in an optional argument. | |
| RTRIM(string)RTRIM(string,string) | Remove trailing whitespace from a string. The whitespace characters may be specified in an optional argument. | |
| TRIM(string)TRIM(string,string) | Remove whitespace from the beginning and end of a string. | |
| UPPER(arg1) | Returns arg1 in upper case | KF1@PERPRODCUST = IF(UPPER( »ATTR1 ») = »APPROVED », « KF2@PERPRODCUST », NULL) |
| MIN(arg1,arg2,…) | In the case of several input key figures, there is no aggregation; it returns the lowest value of the input key figures. | MINCAPACITY@MTHPRODLOC = MIN(« CAPACITYMORNING@MTHPRODLOC », « CAPACITYAFTERNOON@MTHPRODLOC », « CAPACITYNIGHT@MTHPRODLOC ») |
| MAX(arg1,arg2,…) | In the case of several input key figures, there is no aggregation; it returns the highest value of the input key figures. | MAXCAPACITY@MTHPRODLOC = MAX(« CAPACITYMORNING@MTHPRODLOC », « CAPACITYAFTERNOON@MTHPRODLOC », « CAPACITYNIGHT@MTHPRODLOC ») |
Example
Sample configuration for aggregation of standard deviation
Take the sum of the squares; then calculate the square root of the total:
- Calculate the squares:
- Square the values:HKF1@PL = PROPAGATEDDEMANDSTDEV@PL ** 2
- Sum the squares:HKF1@REQUEST = SUM(HKF1@PL)
- Calculate the square root of the total:PROPAGATEDDEMANDSTDEV@REQUEST= HKF1@REQUEST ** 0.5
Example
ISNULL
The ISNULL condition works only when an underlying time series record exists for the planning object.
Imagine that Sales Forecast Quantity and Marketing Forecast Quantity are the stored key figures for planning level PERPROD.
| Planning Object | Period | Key Figure: Sales Forecast Qty | Key Figure: Marketing Forecast Qty |
|---|---|---|---|
| P1 | Jan 2018 | 100 | |
| P1 | Mar 2018 | 100 |
With the above data, IF(ISNULL(SALESFCSTQTY),1,0) exhibits the following behavior:
| Period | ISNULL Value | Notes |
|---|---|---|
| Jan 2018 | 0 | January 2018 has the value “100”. |
| Feb 2018 | Not evaluated | The planning object for the time period February 2018 does not exist. |
| Mar 2018 | 1 | Though there is no value for Sales Forecast Quantity, the Marketing Forecast Quantity key figure (for the same planning level) has a valid value. Therefore, a record exists in the time series for this planning object. |
Sample Expressions
| Key Figure | Calculation Expression |
|---|---|
| Actuals Price | ACTUALSPRICE@REQUEST = IF(“ACTUALSQTY@REQUEST”=0,0, “ACTUALSREV@REQUEST”/“ACTUALSQTY@REQUEST”) |
| Capacity Overloads | CAPACITYOVERLOADS@PERLOCRES = IF(“CAPADEMANDUTILPCT@PERLOCRES”> 1,1,0) |
| Capacity Usage | CAPAUSAGE@PERPRODLOCRES = “CAPADEMAND@PERPRODLOCRES”*“(IF(CAPASUPPLYPERDEMAND@PERLOCRES”>1,1, “CAPASUPPLYPERDEMAND@PERLOCRES”)) |
| Marketing Forecast Profit | MARKETINGFORECASTPROFIT@PERPRODCUST = “MARKETINGFORECASTREV@PERPRODCUST”- “HMARKETINGFORECASTCOST@PERPRODCUST” |
| Marketing Forecast Quantity | MARKETINGFORECASTQTY@PERPRODCUST = IF(ISNULL(« MARKETINGFORECASTQTY@PERPRODCUST »), « SALESFORECASTQTY@PERPRODCUST », « MARKETINGFORECASTQTY@PERPRODCUST ») |
| Constrained Versus Consensus Demand Revenue | CONSTRAINEDVSCONSENSUSREV@PERPRODFML = IF(ISNULL(« CONSENSUSDEMANDREV@PERPRODFML »)OR « CONSENSUSDEMANDREV@PERPRODFML »=0,0, IF(ABS(« SUPPLYREV@PERPRODFML » – « CONSENSUSDEMANDREV@PERPRODFML »)/ »CONSENSUSDEMANDREV@PERPRODFML » > 0,2,1,0)) |
| Supply Quantity | SUPPLYQTY@PERPRODLOC = IF(« HPROJECTEDINVENTORYQTY@PERPRODLOC »>=0, « DEPENDENTDEMANDQTY@PERPRODLOC », « DEPENDENTDEMANDQTY@PERPRODLOC »+ « HPROJECTEDINVENTORYQTY@PERPRODLOC ») |
| Bill Cost per Area Demand Revenue | BILL_COST_PER_AREA@BSCIRTRSCFRCTOUFRUTO2L3AVG_A = IF(isnull(« ASSETAREA5@BSCIRTRSCFRCTOUFRUTO2L3AVG_A ») or « ASSETAREA5@BSCIRTRSCFRCTOUFRUTO2L3AVG_A »=0,0, »BILL_COST5@BSCIRTRSCFRCTOUFRUTO2L3AVG_A »/ »ASSETAREA5@BSCIRT |