Inverse/InverseIF formula in sap analytics cloud

[former_member139490]

In this Blog post, I will introduce you the usage of Inverse and InverseIF Formula in SAP Analytics Cloud.

Generally, calculated measures are not editable. However, there are scenarios where the client/user needs to edit these cells, to visualize how it will influence the dependent measures. Inverse formula helps in such what-if simulations

 

Use Case:

Consider “Revenue” calculated column à Revenue = Unit Cost*Quantity, Unit cost and Quantity would be editable columns whereas Revenue metric would not be editable

For what-if simulations, there will be scenarios to change “revenue”, and validate how “unit cost” and “quantity” gets impacted. Inverse Formula in SAC can be leveraged for such scenarios.

Let us consider SAC model with Product, Version, Date dimensions and Revenue, unit cost, quantity measures

Below is input template that allows inputting unit cost and quantity .

Revenue metric will not be editable in above input template.



To perform what-if simulations, “revenue” can be made editable using inverse formula as depicted below.

 

Inverse formula starts with ‘|’ followed by our Inverse () syntax, where we specify what would be change in Unit Cost if Revenue is modified and same for the Quantity Separated by ‘OR’.

Here Unit Cost will get priority as it is mentioned first. If the Requirement is to prioritize Quantity, then quantity formula can be mentioned first. However, if by any chance the Unit cost cell is locked then the changes will take place in the Quantity field.

With inverse formula input template works as below

Now the Revenue column is input enabled. Changes in revenue column will affect the Unit Cost

If the Unit Cost is locked, then inverse formula applies on the quantity column.

Use of InverseIF

If the Requirement is to enable the calculated column for few line items, then we can use InverseIF formula. For example, if the requirement is to enable revenue metric only for two products - Pencil and Binder – InverseIF can be leveraged

In above formula, filters are applied on “I2’ and ‘I3’ Product which is pencil and binder.

Here Revenue_InverseIF is input enabled for I2 and I3 Products and for rest of the Products input is disabled.

After going to the above blog post ,user will be able to know the usage of inverse/InverseIF formula. With the usage of inverse/InverseIF formula, planning would be allowed on formula cells there by what-if simulations can be performed and this would give better insights for planning.

For more information see https://help.sap.com/viewer/00f68c2e08b941f081002fd3691d86a7/release/en-US/d37a17e4cb7c4a2aabc6825ad...