Functions

Formulas can also include a number of functions, as defined below.

abs

Returns the absolute value.  Example: abs(SCORE)

age

Returns an age based on a date provided.  Example: age(DATEOFBIRTH)

average

Returns the average value of the subject or metric.  Example: average(SCORE)

count

Returns the number of elements that are included in the result. Example: count(ID)

concatenate

Joins several text strings into one text string.

Example: concatenate(SCORE,’ out of ‘,TARGET)

default

Returns the first value, but if the first value isn’t found, then returns the second value.  Example: default(SCORE,’No score’)

distinct

Returns the number of unique elements that are included in the result. Example: distinct(ID)

eofy

Returns the last day of the financial year for the specified date.  The financial year can be set within a report, however it defaults to 31 March.  Example: eofy(today()). An optional second parameter allows years to be added or subtracted.  For example, to get the end of the last financial year for the specified date use eofy(today(), -1)

eom

Returns the end of the month for the specified date.  Example: eom(today()).  An optional second parameter allows months to be added or subtracted.  For example, to get the end of the month after the specified date use eom(today(), 1)

first

Returns the first result that matches the query being executed.  Normally, if there are multiple matches, the results are added.  The first function will return the first match only.

format

Allows a value to be formatted differently to the default.  For example, dates are normally formatted as “D MMM yyyy”,.  To change the format of a date, use the format function, for example: format($date, ‘MMM-yy’)

if

Evaluates the second or third expression based on the boolean result of the first expression.

Example: if(SCORE>50,’Passed’,’Failed’)

max

Returns the maximum of the selected metric..

Example: max(SCORE)

min

Returns the minimum of the selected metric..

Example: max(SCORE)

minutes

Converts the value into a date representing that number of minutes.  This can then be added to another date or displayed as a duration.

Example: minutes(60)

now

Returns the current date and time. Example: now()

sofy

Returns the first day of the financial year for the specified date.  The financial year can be set within a report, however it defaults to 31 March.  Example: sofy($date). An optional second parameter allows years to be added or subtracted.  For example, to get the start of the next financial year for the specified date use sofy($date, 1)

som

Returns the start of the month for the specified date.  Example: som($date). An optional second parameter allows months to be added or subtracted.  For example, to get the start of the month before the specified date use som($date, -1)

today

Returns the current date. Example: today()

variance

Returns the variance between two numbers as a percentage.  Example: variance(ACTUAL, BUDGET)


Click to see Comments

Help comments powered by Disqus

In this section