Advanced Rule Formula Examples

Advanced rule formulas include movement calculations and expense allocation based on entity type.

Features

You can use the following features of advanced rule formulas:

Movement calculations

Movement calculations define the copying or movement of data from one time period to another. In the formula examples below, [ AccRec] is the accounts receivable account, and [ AccPay] is the accounts payable account.

Example

Account	Description	Formula
`Mvmt Acc Rec`	Movement in Accounts Receivable	`IIF([TIME].CURRENTMEMBER.PROPERTIES("CALC")="N", [ACCOUNT].[ACCREC] - ([ACCOUNT].[ACCREC], [TIME].PREVMEMBER), SUM(DESCENDANTS([TIME].CURRENTMEMBER, 20, LEAVES)))` With the formula, you are checking whether the current TIME member is parent or not by checking the property CALC = N. If it is not a parent, then we take the current TIME member's Account Receivables (ACCREC) and subtract the previous month's Account Receivables. If the current TIME member is parent, then you simply sum up all of the children.
`Mvmt Inventory`	Movement in Inventory	`IIF([TIME].CURRENTMEMBER.PROPERTIES("CALC")="N", [ACCOUNT].[INVENTORY] - ([ACCOUNT].[INVENTORY], [TIME].PREVMEMBER), SUM(DESCENDANTS([TIME].CURRENTMEMBER, 20, LEAVES)))` The formula checks inventory.
`Mvmt Acc Pay`	Movement in Accounts Payable	`IIF([TIME].CURRENTMEMBER.PROPERTIES("CALC")="N", [ACCOUNT].[ACCPAY] - ([ACCOUNT].[ACCPAY], [TIME].PREVMEMBER), SUM(DESCENDANTS([TIME].CURRENTMEMBER, 20, LEAVES)))`
`Mvmt Work Cap`	Movement in Working Capital	`SUM([ACCOUNT].[MVMT ACC REC]:[ACCOUNT].[MVMT ACC PAY])` Nested formula is only supported by SAP HANA MDX.

Allocating expenses based on entity type

The following examples assume there is an Entity property named Function. If an entity has the function SM, the Total Department Expense is allocated to the S&M Exp account. If an entity has the function RD, the Total Department Expense is allocated to the R&D Exp account. If an entity has the function CORP, the Total Department Expense is allocated to the G&A Exp account.

Example

Account	Description	Formula
`S&M Exp`	Sales & Marketing Expense	`IIF([ENTITY].CURRENTMEMBER.PROPERTIES("CALC")="N", IIF([ENTITY].CURRENTMEMBER.PROPERTIES ("FUNCTION")="SM",[ACCOUNT].[TOTAL DEPT EXP], NULL), SUM(DESCENDANTS([ENTITY].CURRENTMEMBER, 20, LEAVES)))` You first check if the current ENTITY member is parent or not. Then, you read the property FUNCTION of the current ENTITY member to see whether it is "SM" or not. If it is "SM", then, you are taking the Total Department Expense as a Sales & Marketing expense. Otherwise, it will be assigned with zero. If current ENTITY is parent, a sum of all children appears.
`R&D Exp`	R&D Expense	`IIF([ENTITY].CURRENTMEMBER.PROPERTIES("CALC")="N", IIF([ENTITY].CURRENTMEMBER.PROPERTIES ("FUNCTION")="RD",[ACCOUNT].[TOTAL DEPT EXP], NULL), SUM(DESCENDANTS([ENTITY].CURRENTMEMBER, 20, LEAVES)))` In this formula, you are taking the "RD" values of FUNCTION property.
`G&A Exp`	G&A Expense	`IIF([ENTITY].CURRENTMEMBER.PROPERTIES("CALC")="N", IIF([ENTITY].CURRENTMEMBER.PROPERTIES ("FUNCTION")="CORP", [ACCOUNT].[TOTAL DEPT EXP], NULL), SUM(DESCENDANTS([ENTITY].CURRENTMEMBER, 20, LEAVES)))`

Account

Description

Formula

S&M Exp

Sales & Marketing Expense

IIF([ENTITY].CURRENTMEMBER.PROPERTIES("CALC")="N", IIF([ENTITY].CURRENTMEMBER.PROPERTIES ("FUNCTION")="SM",[ACCOUNT].[TOTAL DEPT EXP], NULL), SUM(DESCENDANTS([ENTITY].CURRENTMEMBER, 20, LEAVES)))

You first check if the current ENTITY member is parent or not. Then, you read the property FUNCTION of the current ENTITY member to see whether it is "SM" or not. If it is "SM", then, you are taking the Total Department Expense as a Sales & Marketing expense. Otherwise, it will be assigned with zero. If current ENTITY is parent, a sum of all children appears.

R&D Exp

R&D Expense

IIF([ENTITY].CURRENTMEMBER.PROPERTIES("CALC")="N", IIF([ENTITY].CURRENTMEMBER.PROPERTIES ("FUNCTION")="RD",[ACCOUNT].[TOTAL DEPT EXP], NULL), SUM(DESCENDANTS([ENTITY].CURRENTMEMBER, 20, LEAVES)))

In this formula, you are taking the "RD" values of FUNCTION property.

G&A Exp

G&A Expense

IIF([ENTITY].CURRENTMEMBER.PROPERTIES("CALC")="N", IIF([ENTITY].CURRENTMEMBER.PROPERTIES ("FUNCTION")="CORP", [ACCOUNT].[TOTAL DEPT EXP], NULL), SUM(DESCENDANTS([ENTITY].CURRENTMEMBER, 20, LEAVES)))

Any software coding and/or code lines / strings ("Code") included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended to better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, except if such damages were caused by SAP intentionally or by its gross negligence.