3495717 - Aggregation not working as expected when account and measures use IF function

Aggregation not working as expected when account and measures use IF function

SAP ANALYTICS CLOUD

1. Add IF([d/Account].[p/customProp] = "X",[X],[Y]) formula to a table
2. Add exception aggregation to required cells
3. Notice that the formula always resolves to Y in the applied cells as well as getting a seemingly wrong sum aggregation of the cells.

Aggregations in Formulas | SAP Help Portal

• Say D is our aggregation column.
• Currently the formula checks if the account member has a custom property 'x'
  • if it does it uses the value of measure X
  • else it uses measure Y.
  • The account member D does not have the custom property 'X' and therefore the measure Y is used for the result. 

The formula:

IF([d/Account].[p/customProp] = "X",[X],[Y])

Will always resolve to the value [Y] for account member D.

Adding a SUM exception aggregation does not change this behavior. With the exception aggregation, the results are filtered by each date member and the results are summed together.

Effectively, adding the sum aggregation in this scenario gets the value of Y for each date and adds them up. 

To achieve the behavior you're looking for:

• A new dimension can be added to the model. For example, "CustomSelect".
• You may then add a new formula. For example, 'Z':
  • IF([d/CustomSelect] = "X",[X],[Y] )
• And add a SUM exception aggregation over the dimension 'CustomSelect'.

For account member D the result will now be the sum of the account members with dimension member X.

Exception aggregation, Sum aggregation, Formula, Dimensions, SAP Analytics Cloud, SAC , KBA , LOD-ANA-ML-CAL , Calculation Management , Problem