Workbook Calculation (with Import)

[ARRamos]

Hi there! It saddens me to let you know that we are down to the last example in our list. But I am also happy because you keep coming back, and it only means you are with me all throughout this Workbook Calculation Adapter Blog Posts Series. In the fourth blog post “Workbook Calculation (with Look Up)”, we talked about Workbook Calculation with a Look Up Table. Actually, there is another way of having multiple tabs on your Workbook Calculation excel sheet without manually setting them up in your function. Sounds great right? Introducing, the Import functionality. This fifth and last blog post will focus mainly on the last example.

This scenario shows how Workbook Calculation processes the data from the Input Model Table and a locally saved Excel file which will be imported. Also, it allows additional fields to be included in the result being calculated through a formula maintained directly in a workbook sheet.

Input

This is the table that will be used as an input function:

Input Model Table

Customer	Product	Quantity	Amount
CUST01	PROD05	15	62.5
CUST02	PROD04	89	250
CUST03	PROD03	99	206.25
CUST04	PROD02	112	132.5
CUST05	PROD01	55	20

In the Signature tab, we maintain the following:

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

By maintaining the above fields, we are:

1. Putting the fields Customer and Product in the Granularity section as they contain unique data records. Thereby 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 this field to create a formula which will eventually be included in the final result.

In the Calculation Rules (worksheet), we initially see two tabs (Input and Result).

The Input and Result tabs will be containing the fields maintained in the Signature section.

 

Prior to performing Import, save an excel file containing the following data. This will be the sample File for Import

Product	Discount Percentage
PROD30	0,01
PROD29	0,02
PROD28	0,03
PROD27	0,04
PROD26	0,05
PROD25	0,06
PROD24	0,07
PROD23	0,08
PROD22	0,09
PROD21	0,1
PROD20	0,11
PROD19	0,12
PROD18	0,13
PROD17	0,14
PROD16	0,15
PROD15	0,16
PROD14	0,17
PROD13	0,18
PROD12	0,19
PROD11	0,2
PROD10	0,21
PROD09	0,22
PROD08	0,23
PROD07	0,24
PROD06	0,25
PROD05	0,26
PROD04	0,27
PROD03	0,28
PROD02	0,29
PROD01	0,3

Make sure that the tab is renamed to Discount Percentage.

Perform Import

Follow the steps below to import an Excel or CSV file:

a. On the expanded workbook calculation rules, choose the Filebutton located at the upper left of the workbook.

b. Select Import.

c. Depending on the type of the file to be imported, choose either Import Excel File or Import CSV File.

d. Select Import Excel file.

Then select the locally saved excel file you created earlier.

After the import, the calculation rules will be filled with the data from the imported Excel file. The Input and Result tabs will still be intact.

Input and Result tabs

Customer	Product	Quantity	Amount	Total Discount
		0	0	0

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.

The dummy data provided below can be maintained:

Input tab

Customer	Product	Quantity	Amount	Total Discount
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.

 

Discount Percentage (The data from the imported file)

Product	Discount Percentage
PROD30	0,01
PROD29	0,02
PROD28	0,03
PROD27	0,04
PROD26	0,05
PROD25	0,06
PROD24	0,07
PROD23	0,08
PROD22	0,09
PROD21	0,1
PROD20	0,11
PROD19	0,12
PROD18	0,13
PROD17	0,14
PROD16	0,15
PROD15	0,16
PROD14	0,17
PROD13	0,18
PROD12	0,19
PROD11	0,2
PROD10	0,21
PROD09	0,22
PROD08	0,23
PROD07	0,24
PROD06	0,25
PROD05	0,26
PROD04	0,27
PROD03	0,28
PROD02	0,29
PROD01	0,3

The formulas shown below will be used for the Result tab:

Result tab

Customer	Product	Quantity	Amount	Total Discount
=Input!A2	=Input!B2	=Input!C2	=Input!D2	=(C2*D2)*VLOOK UP(B2,'Discount Percentage'!A:B,2,0)

By maintaining these formulas, we are cross-referencing the value which will be captured by the first four fields, to the Input tab. For the additional field Total Discount, we maintained a formula which will do the following:

1. Multiply the value of the Quantity and Amount


2. Look for the value from the discount percentage (the data from the imported file) according to the data in the field Product.


3. Multiply the result of 1 and 2.

 

Final Output

Customer	Product	Quantity	Amount	Total Discount
CUST01	PROD05	15	62,5	243,75
CUST02	PROD04	89	250	6.007,50
CUST03	PROD03	99	206,25	5.717,25
CUST04	PROD02	112	132,5	4.303,60
CUST05	PROD01	55	20	330,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 Discount) multiplied the product of Quantity and Amount to the value looked up from Discount Percentage (the data from the imported file).

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

 

Now that you already know how to import a locally saved excel file to your Workbook Calculation, configuring should be much faster and easier for you now.

Our Workbook Calculation Adapter Blog Posts Series has now come to an end. I trust that those five blog posts enlightened you on the basics of Workbook Calculation and gives you confidence for future configuration that you’ll be doing.

Thank you so much for reading and watch out for other interesting SAP Profitability and Performance Management related topics on the next blog posts.

Are there any additional scenarios that you’d like to mention which were not included in this Blog posts Series? Please feel free to post a comment below! 😊

 

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.