Workbook Calculation (with Additional Created Field)

[ARRamos]

Hello there! Welcome back to Workbook Calculation Adapter Blog Posts Series. On the first blog post (Workbook Calculation Adapter Overview), we talked about the overview and how to configure Workbook Calculation Adapter. As promised, in the next blog posts, you’ll learn four examples on how to properly use and execute Workbook Calculation function in PaPM.

1. Workbook Calculation (with Additional Created Field)


2. Workbook Calculation (with Parameter)


3. Workbook Calculation (with Look Up)


4. Workbook Calculation (with Import)

In this second blog post, I will explain the first example we have. What if we want to have an additional Field which will contain a formula capturing some fields from your Input function? Sounds easy right? But this is a very interesting scenario and can be used on a daily basis.

This scenario shows how the Workbook Calculation processes the data from the Input Model Table and allows an additional field to be included in the result being calculated through a formula maintained directly in a workbook sheet.

Input

This is the table that we use as an input function:

Input Model Table

Customer	Product	Quantity	Amount
CUST01	PROD01	1	10
CUST02	PROD02	2	20
CUST03	PROD03	3	30
CUST04	PROD04	4	40
CUST05	PROD05	5	50
CUST05	PROD0X	50	500
CUST06	PROD06	60	600

In the Signature tab, we maintain the following:

Signature
Granularity	Selection	Action
Customer	Customer	Total Amount
Product	Product
	Quantity
	Amount

By maintaining the above fields, we are:

1. Putting the fields Customer and Product in the Granularity as they contain unique data records and that we are making sure that the system processes data records from the input data in a grouped manner and that it considers all records from the input function for processing.


2. Making sure that the fields Customer, Product, Quantity and Amount (which can also be seen in our Input Mode Table) will be included in the result table.


3. Adding another field (make sure that the field is created in the environment, otherwise an error will be displayed). We use it to create a formula which will eventually be included in the final result.

In the Calculation Rules (worksheet), we see two tabs (Input and Result). They contain the fields maintained in the Signature section.

Customer	Product	Quantity	Amount	Total Amount
		0	0	0

Both Input and Result tabs contain the table above. The tabs will not show the data records from the input table. We recommend adding dummy data in the Excel tabs to test the Excel formula logic. These data are only used for modeling purposes and are not considered in the calculation during system runtime.

Dummy Data provided below can be maintained.

Input tab

Customer	Product	Quantity	Amount	Total Amount
ARR21	SHOES	143	300	0

Note

Even though dummy data is maintained in the Input tab, it is still capturing the data from the Input Model table.

Formulas shown below will be used for the Result tab.

Result tab

Customer	Product	Quantity	Amount	Total Amount
=Input!A2	=Input!B2	=Input!C2	=Input!D2	=C2*D2

By maintaining these formulas, we are cross-referencing the value which will be captured by the first four fields, to the Input tab. As for the additional field, we maintained a formula which will multiply the value of the Quantity and Amount fields.

Final Output

Customer	Product	Quantity	Amount	Total Amount
CUST01	PROD01	1	10	10,00
CUST02	PROD02	2	20	40,00
CUST03	PROD03	3	30	90,00
CUST04	PROD04	4	40	160,00
CUST05	PROD05	5	50	250,00
CUST05	PROD0X	50	500	25.000,00
CUST06	PROD06	60	600	36.000,00

The first four fields (Customer, Product, Quantity and Amount) just captured the data from the Input Model table because we just created a formula referencing to the input. The additional field (Total Amount) multiplied the value from the Quantity and Amount accordingly.

We only just created a one liner of formula. Anyway, the function considered all records from the input function for processing because the fields have been maintained in the Granularity fields from the Signature section.

I believe that upon reading this blog post, you are now able to understand how to set up/configure a Workbook Calculation function with an additional Field which was not present from the Input function.

What if we create a formula wherein, we use a Parameter with a specified Value? Will the process be as easy as setting up a simple formula? How can we declare the value of the Parameter? These are just some of the questions that we will be answering on the third blog post of this Workbook Calculation Adapter Blog Posts Series focusing on the second example.

For other SAP Profitability and Performance Management related inquiries, you can post your questions through https://answers.sap.com/questions/ask.html and use primary tag: SAP Profitability and Performance Management.

You can also read other SAP Profitability and Performance Management posts via https://community.sap.com/topics/profitability-and-performance-management.