Versions Compared

Old Version 22

changes.mady.by.user Brendan Codrington

Saved on

compared with

New Version 23

changes.mady.by.user Brendan Codrington

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Anchor
Top
Top

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.

Table of Contents

To add a calculated field to a report, click the + icon in the bottom right of the left hand panel, under the folders.


Panel
borderWidth1
borderStylesolid
titleIn this section

Page Tree
rootEnhanced Report Building



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.

Note
titleFields 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.


Info
iconfalse

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.



Info
iconfalse

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 are included below - expand to see details.

Info


Expand
titleCount distinct answers for field: #! (Field Name)

 This is useful for counting:

  • the number of distinct applications (by ApplicationID)
  • the number of distinct jobs (by JobID)
  • the number of distinct candidates (by CandidateID)
  • i.e. the number of unique answers for any field

1. Click #!

2. Select your field

3. Click ) (because you automatically got an open bracket by choosing #!)



Info


Expand
titleDisplay/calculate different outcomes for different answers: Case When X Then Y

 This is useful for counting:

  • performing the above calculation (Count Distinct) in only selected cases, e.g. application is submitted, gender is female, favourite colour is purple.
  • a different way to update or combine answers e.g. display Northern Territory as NT

1. Click Case

2. Click When - you'll then get another pop-up screen.

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.

Tip
  • 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.
  • Save to go back to your report builder screen.


Tip
titleCalculated Field vs Sub Query

Sometimes your calculated field might be complex enough that it is quite laborious building an argument that captures all of the different variables.

If you find that a calculated field - particularly one counting candidates or jobs that fulfil certain requirements - demands too many arguments to be manageable, another option is to add a sub query where you count the candidates or jobs (linked by an appropriate field) and all of the arguments are included as filters on the sub-query instead.


Note
Replace the Field Names and Answers used in the below examples with your own, e.g. 'Gender' with your gender-related global question, and 'Female' with your relevant answer.


Info
iconfalse

Candidate Full Name

FirstName + ' ' + LastName


Info
iconfalse

Count of Candidates e.g. Gender, Diversity

Females: COUNTDISTINCT ( CASE WHEN Gender = 'Female' THEN ApplicationID END )

Disability: COUNTDISTINCT ( CASE WHEN Disability = 'Yes' THEN ApplicationID END )

ATSIIndigenous: COUNTDISTINCT ( CASE WHEN Aboriginal or Torres Strait Islander = 'Yes' THEN ApplicationID END )

NESB: COUNTDISTINCT ( CASE WHEN Diversity: Non English background = 'Yes' THEN ApplicationID END )

Current Employees: COUNTDISTINCT ( CASE WHEN Current Employee = 'Yes' THEN ApplicationID END )



Info
iconfalse

Count of Candidate Outcomes e.g. Offered

Offered Candidates (by APM step and status): COUNTDISTINCT ( CASE WHEN CurrentAPMStep = 'Offer' AND CurrentAPMStepStatus = 'Successful' THEN ApplicationID END )

Declined Candidates (by form question): COUNTDISTINCT ( CASE WHEN Assessment Outcome = 'Declined' THEN ApplicationID END )

Note
Consider how you define and record candidate and job outcomes like this and use a formula appropriate to your circumstances.



Info
iconfalse

Concatenate Multiple Columns

( CASE WHEN CustomFieldOne IS NOT NULL THEN ( CustomFieldOne) ELSE ( '' ) END )

+

( CASE CustomFieldTwo IS NOT NULL THEN ( ', ' + CustomFieldTwo ) ELSE ( '' ) END )

+

( CASE WHEN CustomFieldThree IS NOT NULL THEN ( ', ' + CustomFieldThree) ELSE ( '' ) END )

--repeat for additional columns