Understand General Rules for Advanced Formula Calculations for Planning

Rules of Handling Null and 0

A null value is treated as a non-existent value, while 0 is treated as an actual number.

If the RESULTLOOKUP function returns a null value, advanced formulas do nothing. Here are some examples:

1. DATA(c)=RESULTLOOKUP(a) + RESULTLOOKUP(b)

2. DATA(c)=RESULTLOOKUP(a) - RESULTLOOKUP(b)

3. DATA(c)=RESULTLOOKUP(a) * RESULTLOOKUP(b)

4. DATA(c)=RESULTLOOKUP(a) / RESULTLOOKUP(b)

In the cases above, when RESULTLOOKUP(b) returns a null value, the action results of the advanced formulas are as follows:

1. DATA(c)=RESULTLOOKUP(a)

2. DATA(c)=RESULTLOOKUP(a)

3. DATA(c)=Null. The advanced formulas generates no data.

4. DATA(c)=Null. The advanced formulas generates no data.

In the cases above, if RESULTLOOKUP(b) returns a value 0 instead of null, the action results of the advanced formulas are as follows:

1. DATA(c)=RESULTLOOKUP(a)

2. DATA(c)=RESULTLOOKUP(a)

3. DATA(c)=0. Means a 0 value is created for c.

4. DATA(c)=Null and no run time error. This means the advanced formulas don’t perform any actions and no data is created.

If RESULTLOOKUP(a) returns 0 and RESULTLOOKUP(b) returns a positive value, the formula generates the results below.

1. DATA(c)=RESULTLOOKUP(b)

2. DATA(c)= -RESULTLOOKUP(b)

3. DATA(c)=0. Means value 0 is created for c.

4. DATA(c)=0. Means value 0 is created for c.

Rules of Cell Value Filter

The cell value filter, in the form of a value comparison, can be used with RESULTLOOKUP in an IF instruction.

Example: IF RESULTLOOKUP([d/ACCOUNT]="QUANTITY") > 10 THEN

In the two cases below, you can see how a cell value filter and a calculation range work together in advanced formulas.

Case1: There is an intersection between the calculation range and the cell value filter range

MEMBERSET [d/ACCOUNT]=("Sales")
MEMBERSET [d/PRODUCT]=("16GB", "32GB")
MEMBERSET [d/DATE]=("201801")

IF ResultLookup()>0 THEN
    Data()=ResultLookup([d/DATE]=PREVIOUS(12))
ENDIF

In this example, the cell value filter part is defined by "IF RESULTLOOKUP() > 0".

The calculation range part is defined with MEMBERSET definitions.

First, the cell value filter further narrows down the calculation range. By adding the RESULTLOOKUP () > 0 condition, the value null of (Sales, 16GB, 201801) is dropped from the calculation range because it's not greater than 0. Then the DATA () will have only the remaining value, 100 of (Sales, 32GB, 201801), as the final calculation scope.

Case2: There is no intersection between the calculation range and the cell value filter range

MEMBERSET [d/ACCOUNT]=("Sales")
MEMBERSET [d/PRODUCT]=("16GB", "32GB")
MEMBERSET [d/DATE]=("201801")

IF ResultLookup([d/ACCOUNT]="Cost") < 20 THEN
    Data()=ResultLookup([d/DATE]=PREVIOUS(12))
ENDIF

In this example, the cell value filter part is defined by "IF RESULTLOOKUP ([d/ACCOUNT]="Cost") < 20".

And the calculation range part is defined with MEMBERSET definitions.

The cell value filter tries to find the range. But in this case, MEMBERSET function only defines the Sales member of the Account dimension, while RESULTLOOKUP in the IF condition checks only the Cost member of the Account dimension. There is no intersection between the calculation range and the cell value filter range.

So, other conditions that met the cell value filter will be applied. In this case, 201801 16G is added.

The intersection between the calculation range and the new combination of the cell value filter range, which is 201801 16G Sales, will be the final default scope used in the DATA () function.

Calculation Based on Fact Record-Set

Advanced formulas basically works with the fact record-set of the model. Here is an example:

MEMBERSET [d/Product] ="A"
DATA([d/Account] ="Revenue")=RESULTLOOKUP([d/Account]="Price") * RESULTLOOKUP([d/Account]="Quantity")

The example above multiplies the sales quantity and price of product A for all time periods. If the user does not specify other dimension information and members via the MEMBERSET definition, the advanced formulas will take all members from these dimensions.

Then company A can calculate the revenue value of 2018 Q4 with an advanced formula:

MEMBERSET [d/Date]=("201810","201811","201812")

DATA([d/Account] ="Revenue")=RESULTLOOKUP([d/Account] ="Price",[d/Entity]="*") * RESULTLOOKUP([d/Account]="Quantity")

In the above formula, the first RESULTLOOKUP function returns the record set below:

And the second RESULTLOOKUP function returns the record set below:

New Data Creation – Record based vs. Master data based

Advanced formulas basically create new data on the source record based. For example, a user wants to copy data from the past year to current year.

MEMBERSET [d/Date]=BASEMEMBER([d/Date]. [h/YQM],"2018")

DATA([d/Audit]="LogicCalc")=RESULTLOOKUP([d/Audit]="Input",[d/Date]=PREVIOUS(12))

The formula above copies 2017 input data to 2018 with the "LogicCalc" audit flag. The DATA function will have a date scope from 2018 Jan. to 2018 Dec. according to the MEMBERSET definition.

But if the source scope 2017 has data only from 2017 Jan. to 2017 Sep., then the DATA function will copy the existing source data range only. No 2017 data is copied to the time range 2018 Oct. to 2018 Dec. Yet in some cases, you might want to overwrite all existing data in 2018 and initiate it as value 0 if there are no data in the source data range. Then you can choose to enable the function CONFIG.GENERATE_UNBOOKED_DATA. As a result in this example, from 2018 Oct. to 2018 Dec., the value will be overwritten by 0.

For more information about this option, see CONFIG.GENERATE_UNBOOKED_DATA.

Sign Value and Sign-Flipping

In planning models, there are four financial types of account members: AST, LEQ, INC, EXP. Each account type is created with a predefined sign value. But in advanced formulas, all data are calculated with absolute value by default, regardless of the account type. For example,

DATA([d/Account] ="Revenue")=RESULTLOOKUP([d/Account]="Income") + RESULTLOOKUP([d/Account]="Expense")

In this example, if the total income is an INC type, which has a total value of 100, and the total expense is an EXP type, which has a total value of 50, then Revenue will return 150 by aggregating the absolute values of each account, 100 and 50.

If you want to calculate with the existing sign-flipping rules of Account dimensions, then enable the option "CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE" beforehand:

CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE=ON

DATA([d/Account] ="Revenue")=RESULTLOOKUP([d/Account]="Income") + RESULTLOOKUP([d/Account]="Expense")

In the example above, Revenue will return 50 because the advanced formula flipped the sign of the value 50 from Expense.

For more information about this option, see CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE.

Scope Definition

With a specific attribute value, a user can filter certain members in the MEMBERSET function. For example, you can write a script like this:

MEMBERSET [d/Entity].[p/Region]="Europe"

This script will filter all the members with the region name "Europe" in the Entity dimension.

Related Members Definitions

• Data Writing

  If a user needs to define the related members from the same dimension or a different dimension, the attribute can be used to define the scope of dynamic-related members.

  For example, if you have an Entity dimension such as the one below, and you want to build an advanced formula script to calculate the Sales total and Sales Cost total, using the related member calculation.

  ID	Description	Calculation	SisterMember
  Sales Europe	Sales Europe	Y	Sales Total
  Sales Asia	Sales Asia	Y	Sales Total
  Sales Allowance	Sales Allowance	Y	Sales Cost Total
  Sales Rebate	Sales Rebate	Y	Sales Cost Total
  Sales Total	Sales Total
  Sales Cost Total	Sales Cost Total

  Then write the following script:

  Sample Code

  MEMBERSET [d/Entity].[p/Calculation] ="Y"
  
  DATA([d/Entity] =[d/Entity].[p/SisterMember])=RESULTLOOKUP()

  First MEMBERSET returns the four members "Sales Europe", "Sales Asia", "Sales Allowance", and "Sales Rebate" with the attribute calculation ="Y". The next script line is to read all entity members and aggregate their values to the related member "Sales Total" and "Sales Cost Total". Therefore, the values of Sales Europe and Sales Asia are aggregated to Sales Total, and the values of Sales Allowance and Sales Rebate are aggregated to Sales Cost Total.

• Data Reading

  This kind of dynamic-related member definition can also apply to the data reading.

  Below is an Entity dimension designed to allocate the cost of the global head quarters to each sales lines of business. The global head quarters cost is allocated to the dedicated member "Allocation Source" of each region.

  ID	Description	Calculation	SisterMember
  Allocation Source - Europe	Allocation Source - Europe
  Sales Germany	Sales Germany	Y	Allocation Source - Europe
  Sales UK	Sales UK	Y	Allocation Source - Europe
  Allocation Source - Asia	Allocation Source - Asia
  Sales China	Sales China	Y	Allocation Source - Asia
  Sales Japan	Sales Japan	Y	Allocation Source - Asia

  Then write the following script:

  Sample Code

  MEMBERSET [d/Entity].[p/Calculation] ="Y"
  
  DATA()=RESULTLOOKUP([d/Entity]=[d/Entity].[p/SisterMember])*@AllocationRatio

  First the MEMBERSET function filters out the members of the entity dimension with the attribute calculation ="Y". Then four members ("Sales Germany", "Sales UK", "Sales China", and "Sales Japan") are chosen as the working scope. The next script line reads the allocation source value for each region ("Allocation Source - Europe" and "Allocation Source - Asia") and allocates it based on the allocation ratio (@AllocationRatio) to the four members.