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

borderWidth	1
borderStyle	solid
title	In this section

Page Tree

root	Enhanced 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 Date & Time to explore what's available.

Image RemovedImage Added

For any selection, you'll need to:

1) Define the field type as Dimension or Metric

Image Removed

2) Define to define the value(s) (fields in the view) that the function needs to refer to.

Note

title	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.

Info

icon	false

Days Between

(SQL Server)

Image Removed

Date

Image Added

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

icon	false

Working

Days

Between

to Now

Image RemovedImage Added

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.

Image Removed

a date and the current date.

Subquery (for complex calcs) vs calculated fields (simple)

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

title	Count 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

Image Modified

1. Click #!

2. Select your field

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

Image Modified

Info

Expand

title	Display/calculate different outcomes for different answers: Case When X Then Y

This is useful for counting:

peforming

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.

Image Modified

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

Image Modified

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

title	Calculated 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

icon	false

Candidate Full Name

FirstName + ' ' + LastName

Info

icon	false

Count of

Candidate Group

Candidates e.g. Gender, Diversity

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

Disability: COUNTDISTINCT ( CASE WHEN

"yfCustomGQAppData"."

Disability

"

= 'Yes' THEN

"yfApplicationsDimension"."

ApplicationID

"

END )

CASE WHEN "yfCustomGQAppData"."Current Employee"

Indigenous: COUNTDISTINCT ( CASE WHEN Aboriginal or Torres Strait Islander = 'Yes' THEN

"yfApplicationsDimension"."

ApplicationID

"

END )

CASE WHEN "yfCustomGQAppData"."Current Employee" = 'No' THEN "yfApplicationsDimension"."ApplicationID" END

CASE WHEN "yfCustomGQAppData"."Aboriginal or Torres Strait Islander" = 'Yes' THEN "yfApplicationsDimension"."ApplicationID" END

Complex Custom Formulas vs. Sub Queries

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

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

Info

icon	false

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

icon	false

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

Versions Compared

Old Version 16

New Version Current

Key

Pre-Defined Formula

Days Between

Date

Days

to Now

Custom Formula

Candidate Full Name

Count of

Candidates e.g. Gender, Diversity

Complex Custom Formulas vs. Sub Queries

Count of Candidate Outcomes e.g. Offered

Concatenate Multiple Columns

Page Comparison

Versions Compared

Old Version 16

New Version Current

Key

Pre-Defined Formula

Days Between

Date

Days

to Now

Custom Formula

Candidate Full Name

Count of

Candidates e.g. Gender, Diversity

Complex Custom Formulas vs. Sub Queries

Count of Candidate Outcomes e.g. Offered

Concatenate Multiple Columns