PaPM calculation between rows of a table

[former_member1345722]

Hello everyone,

I am working on a small project that uses PaPM to perform a number of calculations on a given dataset.

The available data for my required calculation look something like the table shown below:

My first row always has the amount filled (so I always have a basis to begin the calculation). For the rest of the rows the amount must be calculated as the amount of previous row * multiplier of the current row.

E.g.

for row 2 we have Amount[2] = Amount[1] * Multiplier[2] = 100 * 1,1 = 110

for row 3 we have Amount[3] = Amount[2] * Multiplier[3] = 110 * 1,2 = 132

etc.

As you can see the amount of each row is dependent on the calculation of the previous one. Additionally, the amount of rows is dynamic for every different calculation (in the example there are 8 rows but in general the calculation must work for N rows).

I haven't found a way yet of how this can be calculated through PaPM. It seems that it has to be calculated by one function to fulfill the dynamic aspect of the calculation, however I don't know how you can refer to another row of a table by using a formula on your function's rule.

Is there a way to implement cumulative dependent calculations for a dynamic dataset?

[Samantha_Yasay]

Hi Fanis,

Greetings!

Based on the requirement you have provided, you need to use different functions available on SAP Profitability and Performance Management to achieve the result you wanted.

First, you may use the Model Table Function as the Input and Output function of the process.

Once the Input function is properly configured, you can start with a Calculation Function. For the Calculation Function, make sure that the processing type is Executable so that it can populate data records in the modeling logic. Use the Model Table as the input of this function and proceed configuring the Signature tab. After that, create a rule where in, the computation you wanted will be computed. You can use this formula on the Rule Tab: Action:

CASE WHEN ZE_AMT = 0.00 THEN ZE_AMT[-1] * ZE_MULT ELSE ZE_AMT END

ZE_AMT = Amount

ZE_MULT = Multiplier

For more information about Calculation Function, you may refer to this link: https://help.sap.com/viewer/56471df1959f4cfd9e3bf7a6d2d5be42/LATEST/en-US/22ec09a09cd446a7bccdb951ca...

Once the Calculation Function is properly configured, you may proceed using a Writer Function with the Model Writer Type: Delete and Insert. The Input function should be the Calculation Unit and the Output Function is the Model Table. For more information about the Writer Function, you may refer to this link: https://help.sap.com/viewer/56471df1959f4cfd9e3bf7a6d2d5be42/LATEST/en-US/07f5f9fcde2146c298708937c3...

As you require a continues calculation of amount, you need to use a View Function for iteration. The Input function should be the Writer Function. On the View Function Advance tab, the Iteration Type you should use for this requirement is "Application Server for Loop" and Iteration Result is "All Iterations". For more information about the View Function, you may refer to this link: https://help.sap.com/viewer/56471df1959f4cfd9e3bf7a6d2d5be42/LATEST/en-US/23dc009edd8f4e9594e24c912d...

After you properly activated and executed all the functions, check the Model Table for the result.

Hope this helps you!

Thanks and Best Regards,

Samantha

[emanuele_fumeo]

Hi Fanis,

actually I don't know if PaPM allows you to perform such a calculation with a standard function, which might be the case. For this, I leave the stage to people that are more expert than me.

But in case you need a quick and dirty solution, I would definitely look at SQL window functions that you can recall from formulas inside PaPM, in particular LEAD and LAG.

These are standard SQL functions that of course are part of the SQL dialect of SAP HANA database.

Hope it helps! Good luck,

Emanuele