Skip to Content
Product Information
Author's profile photo Alvin Ramos

Workbook Calculation (with Look Up)

Hi there again! I’m glad that you keep coming back for this Workbook Calculation Adapter Blog Posts Series. We are now halfway done with our examples. In the third blog post “Workbook Calculation (with Parameter)”, we talked about configuring a Workbook Calculation function having a formula which is capturing a Parameter value. After we learned about how to do it, there is a question which lingers in our thought. It is configuring Workbook Calculation with a Look Up Table. This’ll be explained in this fourth blog post which will be focusing on the third example.

This scenario shows how Workbook Calculation processes the data from the Input Model Table and a Look Up table. Please note that more than one Look Up table is allowed, but for this scenario, only one Look Up table will be used. Also, it is allowed to include additional fields 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

Look Up

This is the table that will be used as Look Up function:

Look Up Model Table (CAU06)

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

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 fields 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 see three tabs: The Input and Result tabs, as well as the Look Up tab corresponding to the Look Up table being connected which will be named after its function ID (CAU06).

Optional: Tabs can be renamed. To rename a tab, double-click on the tab name then rename it.

The Input and Result tabs will contain the fields maintained in the Signature section, while the Look Up tab will contain the fields maintained in the Look Up tables.

Input and Result tabs

Customer Product Quantity Amount Total Discount
0 0 0

CAU06 (Look Up) Tab

Product Discount Percentage
0

The tab will not show the data records from the input and Look Up tables. It is recommended to add 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 Discount
ARR21 SHOES 143 300 0

CAU06

Product Discount Percentage
PIPES 90


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 Discount
=Input!A2 =Input!B2 =Input!C2 =Input!D2 =(C2*D2)*VLOOK UP(B2,CAU06!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 Discount Percentage from the Look Up table CAU06 according to the value of 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 create a formula referencing to the input. The additional field (Total Discount) multiplied the product of Quantity and Amount with the discount percentage looked up from CAU06.

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.

Not only can we use one Look Up Table, but we can also add several more Look Up Tables for calculation. Just like in an excel sheet, the more the tabs, the more calculations, and separate sheets we can use to obtain a desired result. Exciting isn’t it? But what if instead of creating multiple Look Up tables, you want to use a personal excel file, with multiple tabs already set to be used for calculation? Seems like a way of making your life much easier right? I’ll be sure to teach you how you can do it, on the fifth and last blog post of this Workbook Calculation Adapter Blog Posts Series and that will bring us to the last example as well.

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.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.