Calculated Columns and Pivots

Users have the option to create their own custom Measures and use them in Views.

To define a Measure:

  1. In the Settings menu (right panel), click on and select Calculated Column.
  2. Click on Add.

The Create User Calculation Field wizard will open.

Note

If the active View includes Pivots the user can also select the option Calculated Pivot. Either option will open the Create User Calculation Field wizard where the properties can be defined.

Details

Hide Header:
Drag the slider to hide the header of the column.
Column Group Heading:
Enter the name of the desired title for the Column Group Heading.
Column Heading:
To determine the headings that will be displayed for this new Field.
Tip

A calculated column with Hide Header turned on and no content can be used as a spacer.

Format

Data Type:
To determine whether the field is Numeric, Character, Date, etc.
Format:
To manage how the data will be displayed within this field and the option in the Format window will be based on the data type.
Category: Choose a category according to the data type.
Options: Some categories have options, typically decimal places and date formats.
Preview: A preview of the format defined.

Manage Scripts

This option will take the user to an Expression Builder Wizard that will help create a Javascript to calculate the value of the user-defined measure. This is a more advanced option. Please ask an IT Administrator for assistance.

Script Area:
This area displays the Script.
Object selector:
Select a type of object to display next to the list of available values. Note that a search field will filter the list.
Description Area:
Displays more detail on the selected Field, if available.

The wizard provides a list of all the Fields available in the Data Model (under Fields) and a variety of Functions, Statements, Operators and Constants to help with the script creation.

Clicking on a Field name or a specific Function or Statement will insert them in the Script where the cursor is currently placed.

The Javascript created in the wizard must start with necResult= and finish with a semi-colon ; for it to be valid.

Note

User Calculated Pivots are very similar, except, instead of having a list of all the Fields in the Data Model, a distinct list is viewed for the Measure columns.

Tip

Use Global Variable within the Calculated Pivots to make them more dynamic.

Example

The calculation: (2019-Margin)/(2019-Margin)-(2018-Margin)

Can be changed to: (@@THISYEAR-Margin)/( @@THISYEAR -Margin)-( @@LASTYEAR-Margin)

After defining a User Calculated Measure, it can be used in any View belonging to the Data Model, by any user who has access to the Data Model, provided that they also have access to the Fields used to define the User Calculated Measure.

Important

User Calculated Pivots can only be used in the view where the data is pivoted the same way.

Once you are done, click on Validate to get the confirmation that the Expression is valid then click on Save.