OPEX Cost centre allocation - Advanced formula clarification

[praveen_kumar334]

Hi Team,

I'm reaching out for assistance in understanding a specific piece of code from the IFP cost centre expense plan input form. We have a data action that allocates the cost centre expenses with account grouping(SAP_FI_IFP_IM_OPEX_AllocateCCtrExpensesWithAccountGrouping) , and I’m facing challenges deciphering a highlighted section within the code.

Additionally, I've noted that in the corresponding lookup table, we have 13 different account members linked to 2 different partner cost centres (17101301 and 17101302), resulting in a total of 26 entries (13 * 2).

My specific question is regarding the population of partner cost centre, cost centre, company code, and date dimensions. It seems that other dimensions are appearing as #, and I expected to see only the cost centre and debit credit indicator dimension member values. Can someone help clarify the logic behind this?

I appreciate your help in understanding the nuances of this code.

@hartmut_koerner : Can you please help me in understanding the above code. Thanks for the your valuable time. 

[Susanne_Helbig · Accepted solution]

Dear @praveen_kumar334 

the logic of Allocate CCtr Expenses with Account Grouping is the following:
the allocated expenses are assigned to the accounts maintained in Allocation Account property of dimension SAP_FI_IFP_GLACCOUNT.

Please note the following:

• If an allocation account ismaintained for an account, thesystem posts to this allocationaccount.
• If no allocation account ismaintained, but an allocationaccount exists for the account '# -Unassigned', then this account isused as default allocation account.
• If the two cases mentioned abovedo not apply, the original accountis used, so the account is not changed at all.

So what happens in the code:
First we're aggregating the costs over activity type and partner objects, but we're keeping the primary account.
We aggregate the allocation weights over partner cost center.
Then we can calculate the costs for each primary account.

Once this is done, we can allocate thoses costs (Line 58 in the code)
The formula ((xyz) *0 +1) is equal 1 in case data is available on the specific account and null in case there is no data.
So the first part
(RESULTLOOKUP([d/Measures] = "AMOUNT", [d/SAP_FI_IFP_GLACCOUNT] = [d/SAP_FI_IFP_GLACCOUNT].[p/ALLOCATION_ACCOUNT]) * 0 + 1)
is checking if the data was allocated to the account specified in property p/ALLOCATION_ACCOUNT of the account dimension.
If there is no valid account set in this property, the result is null.

For the second part of the formula
(RESULTLOOKUP([d/Measures] = #COST) * 0 + 1)
the result is 1

In our example the result is then 0

DATA([d/Measures] = "AMOUNT", [d/SAP_FI_IFP_CODEBITCREDITCODE] = #IS_ALLOC_ACCOUNT_VALID)
= (RESULTLOOKUP([d/Measures] = "AMOUNT", [d/SAP_FI_IFP_GLACCOUNT] = [d/SAP_FI_IFP_GLACCOUNT].[p/ALLOCATION_ACCOUNT]) * 0 + 1)
+ (RESULTLOOKUP([d/Measures] = #COST) * 0 + 1) - 1
0 = null +1 -1

If there is a valid allocation account maintained in property p/ALLOCATION_ACCOUNT of the account dimension, then the result would be 1 (1+1 -1)

Regarding your second question:
The allocation allocates the costs of a cost center to the specified partner cost centers, based on the weights maintained in the SAP_FI_IFP_IM_OPEX_PlanAdmin),CCtrAllocation Weights tab.

When using our sample data, this means, that the costs of cost center Build. & Maint (US) will be allocated evenly (50:50) to partner cost centers Manufacturing 1 (US) and Manufacturing 2 (US).

Does this answer your questions?

Maybe our demo script will give you some further insights.

Best regards
Susanne

[praveen_kumar334 · Accepted solution]

Hi @Susanne_Helbig ,

Thank you for your prompt response!

I appreciate the information you've shared, but I still have some concerns regarding the handling of dimension members during data processing with the RESULTLOOKUP keyword. Let me consider small set of data as shown below

Using the provided example:

Data( [d/measure] = "Amount" , [d/debitcredit] = "C" ) = RESULTLOOKUP([d/measure] = "Amount" , [d/debitcredit] = "D")

The initial dataset is filtered by the RESULTLOOKUP, and while querying the output, I’ve noticed that the records are aggregated at the account level, but not at the Cost Centre level, date etc. Specifically, I see three different accounts getting aggregated, yet we did not specify that account = # in the data keyword. On the other hand, for the cost centre, there are two distinct values (17101750 and 17101753) which are not getting aggregated.

Could you please help me understand how the RESULTLOOKUP and the DATA keywords work together in these contexts? In which scenarios does the aggregation happen across different dimension members, and where does it not? Your clarity on this matter would be greatly appreciated.

Thanks again for your help!

Regards,

Praveen

[Susanne_Helbig · Accepted solution]

Hi @praveen_kumar334,

let me explain what the code does based on our sample data:
First you need to understand that you need to specify the cost centers which you want to allocate in the data action trigger.
In our case this is cost center 17101750

The allocation weight is maintained in the SAP_FI_IFP_IM_OPEX_PlanAdmin story:

And this is the master data maintained in dimension SAP_FI_IFP_GLACCOUNT

Line 28
First we're aggregating total debit amount on account # - Unassigned / Credit / Variable Measure #M1

Line 30
Then we copy the data from variable measure #M1 to variable measure #M2 and the allocation account maintained for account # - unassigned
as there is no allocation account maintained in our sample data, data on variable measure #M2 = null

Line 31 + Line 33 check if an allocation account for account # - unassigned is maintained, which is not the case in our sample data
@DEFAULT_ALLOC_ACCOUNT_EXISTS = null *0 +1 = null +0 = 0

this part of the code is important to not loose any data and ensure that the data is allocated according the master data.

The following lines are aggregating the debit amounts over activity, partner objects, profit center and functional area, but keeps cost center and account

This is our most complex data action and I hope the logic is now clear.
We're not aggregating over cost centers or time, but, in our example we're grouping two source accounts (Payroll Expenses - Salaries and Travel Expenses - Miscellaneous) into one allocation account (94202000 Assessment Quality costs).

I highly recommend the below blog post if you're not clear about the resultlookup and aggregation of dimensions 
Advanced Formulas – How they work.

I hope this helps to better understand the logic.

Best regards
Susanne