Calculation syntax in accoPLANNING
In this article, we delve into the syntax and capabilities of calculations within accoPLANNING, focusing on their unique attributes across various contexts.
1. Shadow Calculations
accoPLANNING Professional features improvements made to Shadow Calculations introduced in 1.0.69.9. Because of this, creating Shadow Calculations is different between Professional and Enterprise. As Enterprise is a few versions behind Professional for stability reasons, it uses the older method of Shadow Calculations.
accoPLANNING Professional
Shadow calculations can be accessed via the Advanced Options menu, under Calculations > Shadow Calculations. Within this menu, you can choose your output measure and then create your calculation.
The calculation field holds the possible operators and measures you can use for your calculation. Beyond that, you can insert any number for the mathematical operation.
accoPLANNING Enterprise
Access shadow calculations via the "Calculations" tab under formatting settings. The basic formula structure is as follows:
[Output Measure]=[Measure 1]-[Measure 2]
Remember to encase measure names in square brackets and match them exactly to the names in your data field.
Shadow calculations are limited to basic arithmetic operations (+, -, /, *). Refer to the syntax table below for a complete guide.
To separate multiple calculations, simply end each with a semi-colon:
[Profits]=[Revenue]-[Costs];[Failed Cases]=[All Cases]-[Successful Cases]
2. Cell Calculations / Splashing
Cell calculations, including splashing, are executed directly within cells. Simply input a calculation, like 10+5 in a cell.
Incorporate dynamic variables with an "=" sign. When doing this, you will be asked to choose the value for "=". These are either the current cell value or the value of any splashing measures. For instance, =*100 may multiply the current cell value by 100. The full syntax for cell and splashing calculations is summarized in the table below.
3. Custom calculations
Offering the broadest calculation spectrum, custom calculations support all basic math functions, logical operations, and complex calculations. They enable the use of dynamic variables, including sibling dimensions or measures, which appear automatically in the calculation field. Refer to the accompanying table for a detailed syntax overview.
Syntax
Shadow calculations, cell calculations, and custom dimension calculations
Operator | Name | Syntax | Example | Result |
|---|---|---|---|---|
+ | Add | x + y | 4 + 5 | 9 |
- | Subtract | x - y | 3-Jul | 4 |
* | Multiply | x * y | 2 * 3 | 6 |
/ | Divide | x / y | 2-Jun | 3 |
Cell calculations and custom dimension calculations
Operator | Name | Syntax | Example | Result |
|---|---|---|---|---|
% | Percentage | x% | 8% | 0.08 |
% | Addition with Percentage | x + y% | 100 + 3% | 103 |
% | Subtraction with Percentage | x - y% | 100 - 3% | 97 |
^ | Power | x ^ y | 2 ^ 3 | 8 |
! | Factorial | y! | 5! | 120 |
Custom dimension calculations
Operator | Name | Syntax | Example | Result |
|---|---|---|---|---|
and | Logical and | x and y | true and false | 0 |
not | Logical not | not y | not true | 0 |
or | Logical or | x or y | true or false | 1 |
xor | Logical xor | x xor y | true xor true | 0 |
? : | Conditional expression | x ? y : z | 15 > 100 ? 1 : -1 | -1 |
== | Equal | x == y | 2 == 4 - 2 | 1 |
!= | Unequal | x != y | 2 != 3 | 1 |
< | Smaller | x < y | 2 < 3 | 1 |
> | Larger | x > y | 2 > 3 | 0 |
<= | Smallereq | x <= y | 4 <= 3 | 0 |
>= | Largereq | x >= y | 2 + 4 >= 6 | 1 |
Extreme power users may attempt to use functions for custom dimension calculations. As long as the inputs are text and/or numbers and the outputs are numbers, most functions should work.