Workbook Calculation (with Import)

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.