Contents

Advanced Search - Calculated Columns

Mohammed Jamal Updated by Mohammed Jamal

Calculated Columns allow System Users to add columns in User Views by combining results from other columns. Results are calculated from left to right, so it is important to use values correctly when building expressions.

  1. Select the Advanced Search button on the top main menu.
  2. To create a query in the usual way by selecting the appropriate fields of your choice.
  3. Select the Choose Columns button on the toolbar.

The 'Choose Columns' dialog is displayed:

  1. Enter a label in the Calculated Field that you want to display.
  2. Select the Add + button.
  3. To use a value from existing column you must specify the Name of the column, as you can see below:
  1. In the calculated column you should see a field that will allow you to type in an expression:
  1. Remember to Save these changes to be able to see the new column in your results.
As this is a calculated field it is not possible to filter or sort on the header. You can still however, output its results.


Expressions

You can use multiple Expressions in single column by combining them with the use of brackets, for example:

format_minutes(date_diff(minute,colA,colB))

Here are a list of Expressions which can be used:

Name

Expression

Description

Concatenate

concat(colA,colB,colC,….)

Concatenate multiple text values

DateDiff

date_diff(Interval,colA,colB)

Get date difference between 2 dates.

Valid values for Interval:

year

month weekday

day

hour

minute

second

Divide

divide(colA,colB)

Divide colA/colB

FormatMinutes

format_minutes(colA)

Format a number to days/hours/min format

Multiply

multiply(colA,colB)

Multiple values from 2 columns

Percentage

percentage(colA,colB)

(colA/colB)*100

Subtract

subtract(colA,colB)

colA-colB

Sum

sum(colA,colB,colC,….)

Sum multiple values

TimeDiff

time_diff(Interval,colA,colB)

Get timedifference between 2 time fields.

Valid values for Interval:

hour

minute

second

ToNumeric

to_numeric(colA)

Convert results to numeric value

Was this article useful?

Advanced Search

Contact