Calculated Fields allow you to manipulate or change how results appear in your report, over and above the Group Data or Advanced Function options that are available in a field's format options.
You can use a Pre-Defined formula or build your own, depending on your requirements.
To add a calculated field to a report, click the + icon in the bottom right of the left hand panel, under the folders.
Pre-Defined Formula
A number of functions are built in that you can use with date, time or metric fields. Choose Pre-Defined for the Formula Type to explore what's available.
For any selection, you'll need to:
1) Define the field type as Dimension or Metric
2) Define the value(s) (fields in the view) that the function needs to refer to.
Fields you can choose
You can generally only select fields that are available in the view, including standard system fields and your custom Global Questions.
This excludes those calculated in the report or view via another calculated field or sub-query, like the date a certain job status is changed or a step is finished.
3) Give your field a name (if you didn't at the start) and save.
Days Between (SQL Server)
Use this to calculate the calendar days between two dates captured in the system, like job created and job archived, or another date you record against a custom global question on a form.
Working Days Between
Use this to calculate the business days between two dates captured in the system, like job created and job archived, or another date you record against a custom global question on a form.
You'll also need to choose a location field - simply choose a text field from the options.
Custom Formula
To create your own formula, choose Simple as the Formula Type.
You'll now see a formula builder screen like this.
Available fields will be dark grey, while fields not available will be greyed out. What fields are available will change as you build your formula.
Example calculation formats include:
- #! (Field Name) e.g. Count Distinct Application ID
1. Click #!
2. Select your field
3. Click ) (because you automatically got an open bracket by choosing #!)
- Case When X Then Y e.g. Case When (State = 'Northern Territory' Then 'NT')
1. Click Case
2. Click When - you'll then get another pop-up screen to determine:
WHEN
1. Select Field e.g. State
2. Select Operator e.g. =
3. Type in a value for the field e.g. North Territory
4. Select Add
THEN
5. Type in a value for the answer you want displayed when the above is true
6. Select Add
7. Select Save to return to the formula screen
8. Continue adding more When statements as required, e.g. When State = 'Victoria' Then 'VIC'.
ELSE
9. When you've finished adding When statements, you may choose to add an Else statement to cover any other possible outcomes
END
10. Once done, ensure you add an End to close the Case When argument. You'll get an error if you don't.
There are, of course, many variations of these formulas and what you use will depend upon your unique requirements.
- You'll need to enter your argument sequentially.
- If you need to go back a step, or a number of steps, click the Undo button for that section, once for each step taken. Take care when doing so.
- If you need to start a section again, e.g. the entire When section, click Clear. Do this with caution.
- When you have finished your formula, click Validate to check that the formula is complete and not missing any components.
Complex Custom Formulas vs. Sub Queries
Subquery (for complex calcs) vs calculated fields (simple)
