FunctionsFormulas can also include a number of functions, as defined below. absReturns the absolute value. Example: abs(SCORE) ageReturns an age based on a date provided. Example: age(DATEOFBIRTH) averageReturns the average value of the subject or metric. Example: average(SCORE) countReturns the number of elements that are included in the result. Example: count(ID) concatenateJoins several text strings into one text string. Example: concatenate(SCORE,’ out of ‘,TARGET) defaultReturns the first value, but if the first value isn’t found, then returns the second value. Example: default(SCORE,’No score’) distinctReturns the number of unique elements that are included in the result. Example: distinct(ID) eofyReturns 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) eomReturns 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) firstReturns 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. formatAllows 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’) ifEvaluates the second or third expression based on the boolean result of the first expression. Example: if(SCORE>50,’Passed’,’Failed’) maxReturns the maximum of the selected metric.. Example: max(SCORE) minReturns the minimum of the selected metric.. Example: max(SCORE) minutesConverts 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) nowReturns the current date and time. Example: now() sofyReturns 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) somReturns 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) todayReturns the current date. Example: today() varianceReturns the variance between two numbers as a percentage. Example: variance(ACTUAL, BUDGET) Help comments powered by Disqus |
In this section |
Click to see Comments