Calculation

Table of contents

• How does the Calculation measure work?
• Advanced
  • Data missing text
  • Show calculation result
  • Approved mathematical expressions
• Examples of advanced calculation components
  • Example: Referring to a list item based on a drop-down option in a calculation
  • Example: Referring to a list item in a calculation
  • Example: Referring to different list items in a calculation depending on the org.unit
  • Example: Calculating a value depending on other measures' values

How does the Calculation measure work?

Calculation allows the creation of calculations on the canvas. The calculation formula is created by using an Excel-like syntax. Different measures are referred to by system name. A calculation can include:

• Numerical values.
• Sum results.
• Results from other calculations.
• Dynamic values based on the Reporter's choice in a dropdown.

Calculation measure input

Example of dynamic values: if a dropdown contains a list of energy mixes, each with an emission factor, the values of the option the reporter selects are included in the calculation. 

Calculation in the Measure Builder.

When referring to another measure in the calculation formula, the variables are highlighted in 
light blue. When clicking them, the Canvas will light up the connected measures in yellow.

Referenced variables highlighted in the formula and on the canvas

The settings to the right of the Measure Builder include the following options:

• Name
• Unit - From a list, or a custom unit
• Number format - Number or General
• Select number of decimals to show - The number of decimals that will be shown to the reporter
• Use thousand separator - See the example below.
  • unticked - The number will be shown as 2000
  • ticked - The number will be shown as 2 000 (SWE) or 2,000 (ENG)
• Calculation formula
• Data missing text
• Description
• Show result - Turn this off to make it a hidden calculation. The data is then available in Analytics but not shown to the reporter.

Calculation settings

Example 1.

In this example, four numbers are part of two calculations (notice how they are combined in the parenthesis). The sum result of the two numbers is then calculated at the top. The first image below shows the Measure Builder, and the second shows how it looks to the reporter.

Example 1 in the Measure Builder

Example 1 as shown to the reporter

Note: calculations will not display any result until all required data has been reported.

Advanced

Data missing text

Until all required numbers in the canvas have been reported, the Calculation measure shows this text. The default text is "-".

Show calculation result

When enabled, the calculation is shown to the Reporter in the canvas. It is turned on by default.

Turn it off if intermediate calculations are needed in the canvas and/or data exports but should not be displayed to the Reporter.

Show calculation result setting

Example 2.

In this example, four numbers are part of two calculations (notice how they are combined in the parenthesis). The sum result of the two calculations is then calculated in the calculation at the top.

Four number inputs and three calculations

The resulting survey:

Example 2 as shown to the reporter

Note how the calculations will not display any result until all required data has been reported.

Approved mathematical expressions

In addition to the standard operators (+, -, *, /): 

• Expression for exponentiation pow(a, b) - (e.g. pow(3, 2) = 3² = 9) 
• Expression for square root sqrt(a) - (e.g. sqrt(9) = 3)
• Expression for returning the smallest of all given numbers min(a, b, ...) - (e.g. min(10, 20, 15) = 10)
• Expression for returning the largest of all given numbers max(a, b, ...) - (e.g. max(10, 20, 15) = 20)

Examples of advanced calculation components

Example 3: Referring to a list item based on a drop-down option in a calculation

To refer to a factor that changes based on the reporter's choice in a dropdown list, use the keyword data.

See the example below, where the system name of the dropdown measure is EnergyMix and the value of the list item is {"factor_scope_2": 329.19}:

EnergyMix.data.factor_scope_2

Example 4: Referring to a list item in a calculation

It is recommended to store factors that are used in a calculation in a list that can change over time using the valid-from-date. This list should be stored in one place and not duplicated in many calculations over multiple measures. To refer to a list element, use the special keyword pgLists. Read more about how to manage lists.

See the example below, where the PrettyID of the list is energy_list, the PrettyID of the list item is water_item, and the value of the list item is {"factor_scope_1": 23}:

pgLists.energy_list.water_item.factor_scope_1

Example 5: Referring to different list items in a calculation depending on the org.unit

To refer to different list items in a calculation depending on the org.unit being reported for, give each list item the same PrettyID as its matching org.unit.

If the PrettyID of the org.unit is "Stockholm", the PrettyID of its matching list item must also be "Stockholm". If the PrettyID of the list is energy_list and the value of the list item is {"factor_heating_scope_2": 232}, the code in the measure is:

(listValue("energy_list",orgUnitPrettyId)).factor_heating_scope_2

Example 6: Referencing a number that allows data missing

When a calculation references a number that allows data missing, decide how missing data is handled. The calculation cannot produce a result if one of the input variables is marked as missing by the reporter. This can be the desired behavior: for example, a calculation of an average may need to leave out a value that is marked as missing. On the other hand, if the calculation sums two numbers, the default value can be 0.

By default, the calculation cannot produce a result if the number measure_missing is marked as missing.

measure_missing + 10

To set a default value (0) when the number is marked as missing, update the expression to use the getValue function and provide a default value. This is useful if the calculation is based on a conditional measure that might not always have a value. For example:

getValue(measure_missing, 0) + 10

(hasValue(measure_missing) ? measure_missing : other_control) * 100

Example 7: Calculating a value depending on other measures' values

To give a calculation a value depending on the values of other measures, use the example below as a template.

measure_1 == 'DBYes' or measure_2 == 'DBNo' ? 1 : 0