Skip to Content
Technical Articles
Author's profile photo Siddharth Singh

Inverse/InverseIF formula in sap analytics cloud

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/d37a17e4cb7c4a2aabc6825ad1427c5b.html

 

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Steve Christos
      Steve Christos

      Nice article Siddharth!  Product planning and forecasting by margin is another great use case for the INVERSE functionality!

      Author's profile photo Siddharth Singh
      Siddharth Singh
      Blog Post Author

      Thanks!!!

      Author's profile photo Anurag Kumar
      Anurag Kumar

      Useful ..

      Author's profile photo Nabish Kumar
      Nabish Kumar

      Nice one siddharth! Nicely formatted the concept with simple example. Easily understable.

      Author's profile photo Sukanya Krishnan
      Sukanya Krishnan

      Good one Siddharth! Informative

      Author's profile photo Fathima Nihala
      Fathima Nihala

      Good one Siddharth!! Very useful

      Author's profile photo Vaibhav Lokhande
      Vaibhav Lokhande

      Nice Siddharth... very useful...

      Author's profile photo Anita Dhale
      Anita Dhale

      Very nicely articulated article. Thanks Siddharth for sharing.

      Regards,

      Anita