Adding an Advanced Formulas Step

Advanced formulas allow you to design formulas of transformations and calculations to apply to the source data and write the data to defined scopes of the target model.

There are two different ways of designing advanced formulas – using the visual tool and writing scripts. Listed below are the differences between the two approaches:

Visual Script
Target business users Target professional modeling users, such as IT specialists
No coding skills are required Requires basic coding knowledge
Created by dragging and dropping graphic elements Created by writing scripts

Simply put, the visual tool allows business users with no expertise in programming to design formulas by dragging and dropping rather than directly writing scripts, thus helping them quickly set up a simple business case. However, the tool isn't a replacement for script-based advanced formulas – scripting should remain the primary tool to define and design complicated business scenarios.

A few functions are only available in the script editor, and not supported in the visual tool:

  • CARRYFORWARD

  • ATTRIBUTE

  • ELIMMEMBER

  • Mathematical functions MOD and POWER

  • Conversion functions (ROUND, FLOOR, CEILING, and TRUNC)

  • Time calculation functions (DAY, MONTH, YEAR, PERIOD, DAYSINMONTH, DAYSINYEAR, DATERATIO)

  • CONFIGURATION.HIERARCHY, CONFIGURATION.TIME_ZONE_OFFSET

  • ELSEIF, ELSE

  • Some of the MEMBERSET functions

Also, the visual editor has a few more limitations on the use of external parameters, as well as conditional filters using IF statements. If you convert these functions to the visual editor, they will be displayed as read-only grey blocks. For more details, refer to Display of the Unsupported Script in the Visual Tool.

If you need to define a complex scenario with these features, it's best to use the script editor.

Using Parameters

You might want to quickly update a value or change a member in several different places throughout advanced formulas, or allow a user to set their own values in prompts when they execute the data action. In this case, you can use parameters for those values.

Before that, you need to first create parameters in the data action of your advanced formulas. To learn how to create parameters, see Creating a Parameter.

Then, in both advanced formulas created via script and via the visual tool, you can choose a member-type parameter instead of a dimension member, or a number-type parameter instead of a number or number variable. In advanced formulas created via scripts, besides the member selector, you can enter % to view a list of all available parameters.

Tasks
Related Information