Numeric Functions

These functions are available for Calculated Fields and Crosstabs, with the exception of Len which is only available for Calculated Fields.

Function

Min( <Expression> , <Expression> )

Description

Returns the minimum value of the provided expressions.

Arguments

The expressions can be a numeric field name, a date field name, a numeric value or a date value. A combination of a field name and a constant value can be used.
Examples
  • Min( Cost_Price, 1.99 ) would return 1.99
  • Min( Cost_Price, 5.99 ) would return 3.99
  • Min( Cost_Price, Sale_Price ) would return 3.99
  • Min( Trans, ToDate(2015,01,22) ) would return 22/01/2015 (20150122000000)
  • Min( Trans, Return ) would return 25/01/2015 (20150125000000)
Function Max( <Expression> , <Expression> )
Description Returns the maximum value of the provided expressions.
Arguments The expressions can be a numeric field name, a date field name, a numeric value or a date value. A combination of a field name and a constant value can be used.
Examples

Cost_Price = 3.99, Sale_Price = 4.99, Trans = 25/01/2015 Return = 27/01/2015

  • Max( Cost_Price, 1.99 ) would return 3.99
  • Max( Cost_Price, 5.99 ) would return 5.99
  • Max( Cost_Price, Sale_Price ) would return 5.99
  • Max( Trans, ToDate(2015,01,22) ) would return 25/01/2015 (20150125000000)
  • Max( Trans, Return ) would return 27/01/2015 (20150127000000)
Function Round( <Expression> )
Description Returns a numeric value rounded to the nearest whole number.
Arguments The expression has to be a numeric value.
Examples

Cost_Price = 1.49, Sale_Price = 1.69

  • Round( Cost_Price ) would return 1.00
  • Round( Sale_Price ) would return 2.00
Function Floor( <Expression> )
Description Returns a numeric value rounded down to the nearest whole number.
Arguments The expression has to be a numeric value.
Examples

Cost_Price = 1.49, Sale_Price = 1.69

  • Floor( Cost_Price ) would return 1.00
  • Floor( Sale_Price ) would return 1.00
Function Ceil( <Expression> )
Description Returns a numeric value rounded up to the nearest whole number.
Arguments The expression has to be a numeric value.
Examples

Cost_Price = 1.49, Sale_Price = 1.69

  • Ceil( Cost_Price ) would return 2.00
  • Ceil( Sale_Price ) would return 2.00
Function Abs( <Expression> )
Description A mathematical function that returns the absolute (positive) value of a numeric expression.
Arguments The expression has to be a numeric value.
Examples

Cost_Price = 0, Sale_Price = 1.69, Refund_Price = -1.69

  • Abs( Cost_Price ) would return 0
  • Abs( Sale_Price ) would return 1.69
  • Abs( Refund_Price ) would return 1.69
Function Len( <String Expression> )
Description Returns the number of characters of the specified string expression.
Arguments The expression can be a string or numeric value.
Examples

Name = ‘John Smith’, Loyalty_Points = 691

  • Len( Name ) would return 10
  • Len( Loyalty_Points ) would return 3
Function ToYear( <Date Expression> )
Description Returns the year datepart of a date(time) expression.
Arguments The expression must be a date or datetime expression.
Examples

Transaction_Date = 22/01/2015

  • ToYear( Transaction_Date ) would return 2015
Function ToMonth( <Date Expression> )
Description Returns the month datepart of a date(time) expression.
Arguments The expression must be a date or datetime expression.
Examples

Transaction_Date = 22/01/2015

  • ToMonth( Transaction_Date ) would return 1
Function ToWeek( <Date Expression> )
Description Returns the week datepart of a date(time) expression.
Arguments The expression must be a date or datetime expression.
Examples

Transaction_Date = 22/01/2015

  • ToWeek( Transaction_Date ) would return 4
Function ToDay( <Date Expression> )
Description Returns the day date part of a date(time) expression.
Arguments The expression must be a date or datetime expression.
Examples

Transaction_Date = 22/01/2015

  • ToDay( Transaction_Date ) would return 22
Function ToDayOfWeek( <Date Expression> )
Description Returns the weekday number of a date(time) expression (where Sunday = 1).
Arguments The expression must be a date or datetime expression.
Examples

Transaction_Date = 22/01/2015 (Thursday)

  • ToDayOfWeek( Transaction_Date ) would return 5
Function ToHour( <Date Expression> )
Description Returns the hour datepart of a datetime expression.
Arguments The expression must be a date or datetime expression.
Examples

Transaction_Date = 22/01/2015 18:32:15

  • ToHour( Transaction_Date ) would return 18
Function ToMinute( <Date Expression> )
Description Returns the minute datepart of a datetime expression.
Arguments The expression must be a date or datetime expression.
Examples

Transaction_Date = 22/01/2015 18:32:15

  • ToMinute( Transaction_Date ) would return 32
Function ToSecond( <Date Expression> )
Description Returns the second datepart of a datetime expression.
Arguments The expression must be a date or datetime expression.
Examples

Transaction_Date = 22/01/2015 18:32:15

  • ToSecond( Transaction_Date ) would return 15
Function ToJulianDay( <Date Expression> )
Description Returns the Julian Day value of a date(time) expression. The Julian day is the continuous count of days since the beginning of the Julian Period and is used when comparing two dates.
Arguments The expression must be a date or datetime expression.
Examples

Raised_Date = 22/01/2015 Closed_Date = 25/01/2015

  • ToJulianDay( Raised_Date ) would return 16457
  • ToJulianDay( Closed_Date ) would return 16460
  • ToJulianDay( Closed_Date ) - ToJulianDay( Raised_Date ) would return 3 (days)
Function ToJulianDayTime( <Date Expression> )
Description Returns the Julian Daytime value of a datetime expression. The Julian daytime is the continuous count of seconds since the beginning of the Julian Period and is used when comparing two datetimes.
Arguments The expression must be a datetime expression.
Examples

Raised_Date = 28/01/2015 10:49:51 Closed_Date = 28/01/2015 14:01:44

  • ToJulianDay( Raised_Date ) would return 1422442191
  • ToJulianDay( Closed_Date ) would return 1422453704
  • ToJulianDay( Closed_Date ) - ToJulianDay( Raised_Date ) would return 11513 (seconds)

Note: This could be formatted as a Time Measure in a Crosstab.

Function FromJulianDay( <Date Expression> )
Description Returns a date as a numeric field from a Julian day. The Julian day is the continuous count of days since the beginning of the Julian Period and can be used when comparing two dates.
Arguments  
Examples

Raised_Date = 16457 Closed_Date = 16460

  • FromJulianDay( Raised_Date ) would return 20150122
  • FromJulianDay( Closed_Date ) would return 20150125
Function FromJulianDayTime( <Date Expression> )
Description Returns a datetime expression from a Julian Daytime value. The Julian daytime is the continuous count of seconds since the beginning of the Julian Period and can be used when comparing two datetimes.
Arguments  
Examples

Raised_Date = 1422442191 Closed_Date = 1422453704

  • FromJulianDay( Raised_Date ) would return 20150128104951
  • FromJulianDay( Closed_Date ) would return 20150128140144
Function DateBetween( <Date Expression> , <Date Expression> , DAYS )
Description Returns the number of inclusive period values between two date expressions, except when a time is specified.
Arguments The expression must be a date(time) expression. A period must also be provided. This must be in UPPERCASE and can be one of the following: DAYS, WEEKS, MONTHS, YEARS, SECONDS, MINUTES OR HOURS.
Examples  

Note: The output of this function when using Seconds could be formatted as a Time Measure in a Crosstab.

Using DateBetween(<>,<>, SECONDS) over thousands of records will dramatically increase processing time.

Function DaysBetween( <Date Expression> , <Date Expression>, <Optional Day(s)> )
Description Returns the number of inclusive period values between two date expressions.
Arguments The expression must be a date(time) expression. It can also contain optional arguments to exclude named days of the week from the calculation. Useful for working out the number of working days in a period, for example.
Examples  

Note: This function is inclusive and returns ‘1’ if starting and finishing on the same day.

Function Cos( <Expression> )
Description Returns the cosine of the provided expression. NB The result is returned in radians NOT degrees.
Arguments The expression has to be a numeric value.
Examples

Value = 20

  • Would return 0.41
Function Sin( <Expression> )
Description Returns the sine of the provided expression. NB The result is returned in radians NOT degrees
Arguments The expression has to be a numeric value.
Examples

Value = 30

  • Would return -0.99
Function Sqrt( <Expression> )
Description Returns the square root of the provided expression
Arguments The expression has to be a numeric value.
Examples

Value = 81

  • Would return 9
Function Tan( <Expression> )
Description Returns the tangent of the provided expression. NB The result is returned in radians NOT degrees
Arguments The expression has to be a numeric value.
Examples

Value = 50

  • Would return 0.96
Function Random( <Expression> )
Description Generates a random number between 0 and 1
Arguments  
Examples  
Function Column( <Expression> , <Expression> )
Description References the value of a total in a column
Arguments The first value is the position of the column, starting at 0 (zero) and the second value is the position of the total.
Examples  
Function ColumnTotal ( < Expression> )
Description References the column total of a total
Arguments  
Examples  
Function RowTotal (<Expression>)
Description References the row total of a total
Arguments  
Examples  
Function GrandTotal (<Expression>)
Description References the grand total of a total
Arguments  
Examples