I just want to subtract 2 formulas!

Hello All,

Not sure what I am missing here. This should be easy but I am at a loss.

GOAL: I would like to get the % Quantity Change at a case level (PO line Item level)

I have the following formulas:

  1. Original Quantity = PU_FIRST (“EKPO”, “_CEL_P2P_ACTIVITIES”.“CHANGED_FROM”, “_CEL_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘Change Quantity’)

  2. Last Quantity = PU_LAST (“EKPO”, “_CEL_P2P_ACTIVITIES”.“CHANGED_FROM”, “_CEL_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘Change Quantity’)

ISSUE: I would expect that I should be able to use a simple formula like

(KPI(“Last Qty”) - KPI(“Original Qty”))
/
KPI(“Original Qty”)

and format it as a percent.

The error I get is “Operator requirements are not met. Operator ‘SUB’ is not compatible with inputs of type STRING and STRING.”

Both KPIs are formatted as decimals. I have also tried to CAST AS FLOAT and then the error changes to “…not compatible with inputs of type FLOAT.”

Please advise what I am doing wrong. This seems like a simple implementation and it is really embarrassing that I am struggling this hard trying to accomplish this.

Hi Jsekuto,
a quick fix would be to use the STR_TO_INT function to convert your string values into integer. You will loose some precision though, as the decimal portion is ignored. Unfortunately there is currently no STR_TO_FLOAT function directly in Celonis.

To fully solve your problem, you would have to add a column CHANGED_FROM/TO_FLOAT on the database in which you add the values as float for the change activities for which this is applicable (e.g. Change Quantity, Change Price).

Best regards,
Pol

Thanks Pol.

I tried to use the above and received null values. The PQL reference provides this insight:

STR_TO_INT converts the input strings to integers. If the input is not a number (or outside of the supported range), null is returned. If the input is a float, the decimal places are ignored.

I am dealing with quantities so theses are definitely numbers. Please advise.

Hello jsekuto.

I think that you are trying to calculate a KPI that is not possible to calculate, unless the data was completely perfect, but that is very weird in the ERP.

If you a write a filter like this “FILTER “_CEL_P2P_ACTIVITIES”.“CHANGED_FROM” = ‘1’” and put it into the Load Script of analisys settings. Them you buid a olap table with columns CHANGED_FROM, Original Quantity and Last Quantity, you could see that even you are filtering data, the variables show the first and the last values of CHANGED_FROM, that is that the substraction is not between the data seeing in the table, the rest is in another level.

In my opinion you are trying to calcularte a number that is no related to the process, it looks like a KPI for a BI report

I hope to be wrong, because I tried it before and I couldn’t,

Hi all,

calculating this KPI is possible, the problem is that your CHANGED_FROM column is a String column, as Pol pointed out.
Did you check STR_TO_INT separately? Then you would see if the conversion works or not. Can you give examples for strings where STR_TO_INT returns NULL?
You can also think about changing the data type to INT or FLOAT in the data model itself (on database level).

Cheers
David

Thanks all for the help on this. The STR_TO_INT was the solution.

This brings up a curious circumstance that I now have observed twice. When I tried to implement the solution, my experience was that I had null values returned. My boss (who is very hands-on) implemented the exact same solution and it returned exactly what we expected.

There was another instance about a month ago before the holidays where he tried to insert a simple formula, was not able to get it to work, but I was able to execute the same. Has anyone else experienced this?