Workbook Calculation (with Look Up)

[ARRamos]

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

Note

Only Data functions (e.g Model Tables and Model Views) can be used as Look Up function for Workbook Calculation.

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.