Public

Creating a custom (calculated) column

Custom calculations can be created but note, as these calculations take place on top of the results, all fields required for your advanced calculation must already be included in the report. The structure of the report will not change once the calculation is included. If there is one row per job previously, adding a calculated field will not increase or reduce the total rows returned.

Note: While it will display as its system name in the calculation, typing the client name will autocomplete the fields in order to allow you to find what you are looking for.

Note: Looker has an undocumented limit on the total number of custom measures and table calculations per report. It does vary, but at around 20 or more the report may experience JSON errors when drill down to  detail from the results.

To create a table calculation:

1. In the Explore screen's field listing, next to Custom Fields click Add. 
2. Click Table Calculation.
3. In the Edit Table Calculation pop up, specific functions can be written to build advanced queries.
4. Refer to the Help + Syntax Reference hyperlink for help from the Looker website. Alternatively...
5. Click Save to keep the settings.

An example showing the use of a Custom Dimension to concatenate 2 fields:

Looker resource: Using table calculations

Formatting date fields

In the Data section, if the Results tab displays a date as YYYY-MM-DD you can change the format.

Custom Dimension

This approach will be useful where you only want the reformatted date to be included in the data section of the report. Custom Dimensions can also be included with 'All Results' downloads.

Example: A report on Job with the Applicant fields: Applicant e-mail., Application status (current), & date application submitted.

The below sytnax converts the date from YYYY-MM-DD to be MM/DD/YYYY. A small adjustment to the order of the IF statements will work for converting to DD/MM/YYYY

1. In the Explore screen's field listing, next to Custom Fields click Add.
2. Choose Custom Dimension (
3. Alternatively, right-click an existing Dimension as a starting point.
4. In the Edit custom dimension popup enter the following Expression:
   concat(
if(extract_months(${application_view.submit_date})<10
,concat("0",extract_months(${application_view.submit_date}))
,concat("",extract_months(${application_view.submit_date}))
)
,"/",
if(extract_days(${application_view.submit_date})<10
,concat("0",extract_days(${application_view.submit_date}))
,concat("",extract_days(${application_view.submit_date}))
)
,"/",
extract_years(${application_view.submit_date})
)
5. The formatting dropdown can be left as Default
6. Enter a Name, e.g. Submitted date formatted
7. Click Save
   The column is added to the data section with the blue grey colour titled Submitted date formatted and the values will be in the desired format.
   

Custom Table Calculation

This approach will be useful where you want the original date to be present in your report, in addition to the formatted date

Example: A report on Job with the Job fields: Job No., Job title, and Added Date.

1. In the Explore screen's field listing, next to Custom Fields click Add.
2. Click Table Calculation.
3. In the Edit Table Calculation pop up, enter the following Expression formula:
   diff_days(to_date("1899-12-30"),${job_view.added_date})
4. Click the formatting drop down and select Custom.
5. In the field beneath, enter the format syntax, for example: DD/MM/YYY.
6. Enter a Name, for example: Date Formatted.
7. Click Save.
   The column is added to the table in green and is titled Date Formatted with values in the custom format.

Calculating the number of days between two dates

Example: Calculating the number of days between when a job was created and when an offer was first made.

1. In the Explore screen, add:
   • the Job group dimension field Job title,
   • the Application Steps group measure field Date of First Offer Made.
2. From the field listing, next to Custom Fields click Add.
3. Click Table Calculation.
4. In the Edit Table Calculation pop up, enter the following Expression formula: diff_days(${job.created_date},${application_recruitment_steps.first_offermade})
5. Enter a Name, for example: Days between Created & First offer made.
   
   
   
   
6. Click Save.

The result is a number of days between the 2 dates.



Focusing on Job 858106 - it was created on 2021-03-18 and the Date of first offer made was 2021-03-29. There is a difference of 11 days.

To calculate the number of working days, take this difference in days, and divide it by 7*5 rounded to the nearest whole number. The Expression formula would be:

round(diff_days(${job.created_date},${application_recruitment_steps.first_offermade}) / 7 * 5, 0)

with the result:

Calculating the 2nd 'Friday' from offer start Date

For scenario's where you would like to show in a report the date of the Friday 2 weeks from the Start date on the offer card, you can use the following Expression in a custom dimension.

add_days(14-mod(diff_days(date(2020,1,3),${offer.start_date}), 7),${offer.start_date})

Explanation of how this expression works.

add_days( //Add some days to a value
14 -//we want 2 weeks out
mod(diff_days(
date(2020,1,3),${offer.start_date}), 7)
//This is kinda cool, it grabs a friday and says ok, treat any friday as the '0' for us to start counting, now count how many days we needa move until we find a friday from the offer start date
,${offer.start_date})
// This is the last part of the "add days"
//So we're adding 14 days, then subtracting how many days closer to friday that we already are
eg: current day is thursday, so we're already 6 days into the "week" so 2 fridays away would be 14-6 = 8

Managing Ø (null) values

In the Data section, if the Results tab displays a Ø (null) value you can make it more relevant by adding a replacement term.

Example: A report on Job with the Applicant field Country.

1. In the Explore screen's field listing, next to Custom Fields click Add.
2. Click Table Calculation.
3. In the Edit Table Calculation pop up, enter the following Expression formula:
   coalesce(${applicant.country},"No country supplied")
4. Enter a Name, for example Country Replaced.
5. Click Save.
   The column is added to the table in green, and the null is replaced with the new value, No country supplied.

Aggregating pie chart segments

You can use a custom calculation column to collapse the contributing rows of a table into a single segment in a pie chart. This is useful if a pie chart has many segments of minimal or insignificant data. In the following example, these will be combined to make the display more informative.

A pie chart needs at least one dimension against one measure and the total rows should be 50 or less.

Example: A report on Applicant Search / All Candidates with the Applicant fields: Country and Total Applicants.

1. In the Explore screen, click to expand the Data section.
2. Select the Total checkbox and click Run to get the total number of records.
3. From the field listing, next to Custom Fields click Add.
4. Click Table Calculation.
5. In the Edit Table Calculation pop up, enter the following Expression formula: if(row() != max( row()),${applicant.country},"other")
6. Enter a Name, for example: Bucket segment.
7. Click Save.
   The column is added to the table in green and the null is replaced with the new term, in this example: Bucket segment.
8. As pie chart should only contain one dimension, the original column can be hidden. For this example, next to Applicant Country click the click cog icon then Hide from Visualisation.
9. Set the Row Limit to suit, for example 10 and click Run to refresh the pie chart  and results.

Converting a string field to a currency field

Default behaviour of Enhanced Reporting is to consider a comma (,) as the end of a number.

The replace is required because the ER system logic treats commas as the end of number, so will turn 123,456.78 into 123.00 instead.

To avoid these numbers appearing incomplete in your report, setup a custom dimension.

Example: Converting an offer card string field to a currency field.

1. In the Explore screen's field listing, next to Custom Fields click Add.
2. Click Custom Dimension.
3. In the Edit Table Calculation pop up, enter the following Expression formula: replace(${offer_other.string_other5}, ",", "")
4. Enter a Name.
5. Click Save.

Once done, replace the ${offer_other.string_other5} with the relevant dimension from your system.

Reporting on events per hour

Example: Reporting on the hour a job was created, without the date or the minute.

1. In the Explore screen's field listing, next to Custom Fields click Add.
2. Click Custom Dimension.
3. In the Edit Table Calculation pop up, enter the following Expression formula: extract_hours(${job.created_time})
4. Enter a Name, for example: Hour created.
5. Click Save.

The result is a chart that shows when actions are occurring during the day.

Using a Bin to group numeric dimensions

The Bin custom field type can be used to easily group values into certain ranges, that will then allow you to manipulate the data.

Looker resource: Custom binning

Example: Binning Current Salary into bands or tiers of 20000.

1. In the Explore screen's side menu, search for the field Current salary.
2. In the search results, mouse hover Current Salary and click the More icon.
3. Click the Bin option.
   
   
   
   
4. In the Bin Current salary pop up, define the range, for example:
   • Bin size - 20000
   • Minimum value - 20000
   • Maximum value - 100000
     Note: All values falling outside of the minimum and maximum will be grouped together.
5. Enter a Field name to be used a column name for the binned values.
   
   
   
   
6. Click Save to keep the settings.
   The column is added to the table.
7. Click Run to refresh the results, getting the values grouped by their Bin ranges.