Getting Started with Advanced Formulas in SAP Analytic Cloud

Starting with advanced formulas can be intimidating, especially without coding experience. When I started at McCoy, I had little coding experience, but following the steps below made it easier than expected! That's why I'm happy to share these four steps with you for a smooth start with advanced formulas within data actions!

Why do we use Advanced Formulas in SAP Analytics Cloud?

Simple calculations can be done in SAP Analytics Cloud (SAC) using 'Story calculations' and 'Member formulas.' We use advanced formulas in the following cases:

• For more complicated calculations

• When intermediate calculations are necessary

• When the result needs to be written to the database

• When a calculation needs to be done over a subset of data (e.g. specific company codes)

In this blog, you'll find some tips to help you get started with creating advanced formulas.

Tip 1: Visual Tool

There are two methods to create an Advanced Formula:

• Scripting

• Visual tool

The visual tool is the easiest to use. This tool works with graphical drag-and-drop elements where you can add calculations and conditions. The Visual tool automatically generates a script, which is usable via scripting for more complex calculations. Make it easy for yourself by starting with the Visual tool.

Tip 2: Data Action Parameters

Before you begin with your data actions, make it clear which measures and dimension members you want to include in your calculation. Set up parameters within the data action for your measures and dimensions. You can use these parameters in the 'context' of your Advanced Formulas to keep your data clean and ensure that your outcome is well written. We use parameters for three purposes:

• 'Context': Here, you'll write the data. Defined parameters ensure you can make your scope flexible.

• 'Variable' - Target: This is what you've defined as standard in context. The "TargetVersion" parameter is generated by default.

• 'Variable' - Source: These are the data rules the script picks up from the database to perform calculations.

Think carefully about the data you need and the data you want to create.

Tip 3: SAC Help Features

SAC has built-in options to assist you with Advanced Formulas. Within parameters and the Visual tool, direct feedback is provided. For example, when using values or criteria not allowed. You can also always use the HELP menu at the top right in SAC to find more information about a topic. When creating an advanced formula, press Ctrl + space on your keyboard to open a list of menu items you can use in the current field. SAC has also compiled an overview of advanced formula syntax: SAC-help. Make use of SAC's built-in help features.

Tip 4: Practice!

Define clear parameters. Start with the Visual tool but switch to scripting as well. Use SAC help features. Practice a lot, and you'll get a feel for Advanced Formulas in SAP Analytics Cloud, becoming more adept in no time!