thankQ Help
Aggregate Functions
Function |
Description |
Example |
XLS(x) Format Export-Friendly |
Avg(Value) |
Evaluates the average of the values in the collection. |
[].Avg([UnitPrice]) |
- |
Count() |
Returns the number of objects in a collection. |
[].Count() |
- |
Exists() |
Determines whether the object exists in the collection. |
[][[CategoryID] == 7].Exists() |
- |
Max(Value) |
Returns the maximum expression value in a collection. |
[].Max([UnitPrice]) |
- |
Min(Value) |
Returns the minimum expression value in a collection. |
[].Min([UnitPrice]) |
- |
Single() |
Returns a single object from the collection. |
[][^.ParentId = Id].Single([This]) |
- |
Sum(Value) |
Returns the sum of all the expression values in the collection. |
[].Sum([UnitsInStock]) |
- |
Date-time Functions
Function |
Description |
Example |
XLS(x) Format Export-Friendly |
AddDays(DateTime, DaysCount) |
Returns a date-time value that is the specified number of days away from the specified DateTime. |
AddDays([OrderDate], 30) |
Yes |
AddHours(DateTime, HoursCount) |
Returns a date-time value that is the specified number of hours away from the specified DateTime. |
AddHours([StartTime], 2) |
Yes |
AddMilliSeconds(DateTime, MilliSecondsCount) |
Returns a date-time value that is the specified number of milliseconds away from the specified DateTime. |
AddMilliSeconds(([StartTime], 5000)) |
- |
AddMinutes(DateTime, MinutesCount) |
Returns a date-time value that is the specified number of minutes away from the specified DateTime. |
AddMinutes([StartTime], 30) |
Yes |
AddMonths(DateTime, MonthsCount) |
Returns a date-time value that is the specified number of months away from the specified DateTime. |
AddMonths([OrderDate], 1) |
Yes |
AddSeconds(DateTime, SecondsCount) |
Returns a date-time value that is the specified number of seconds away from the specified DateTime. |
AddSeconds([StartTime], 60) |
Yes |
AddTicks(DateTime, TicksCount) |
Returns a date-time value that is the specified number of ticks away from the specified DateTime. |
AddTicks([StartTime], 5000) |
- |
AddTimeSpan(DateTime, TimeSpan) |
Returns a date-time value that is away from the specified DateTime for the given TimeSpan. |
AddTimeSpan([StartTime], [Duration]) |
- |
AddYears(DateTime, YearsCount) |
Returns a date-time value that is the specified number of years away from the specified DateTime. |
AddYears([EndDate], -1) |
Yes |
DateDiffDay(startDate, endDate) |
Returns the number of day boundaries between two non-nullable dates. |
DateDiffDay([StartTime], Now()) |
Yes |
DateDiffHour(startDate, endDate) |
Returns the number of hour boundaries between two non-nullable dates. |
DateDiffHour([StartTime], Now()) |
Yes |
DateDiffMilliSecond(startDate, endDate) |
Returns the number of millisecond boundaries between two non-nullable dates. |
DateDiffMilliSecond([StartTime], Now()) |
- |
DateDiffMinute(startDate, endDate) |
Returns the number of minute boundaries between two non-nullable dates. |
DateDiffMinute([StartTime], Now()) |
Yes |
DateDiffMonth(startDate, endDate) |
Returns the number of month boundaries between two non-nullable dates. |
DateDiffMonth([StartTime], Now()) |
Yes |
DateDiffSecond(startDate, endDate) |
Returns the number of second boundaries between two non-nullable dates. |
DateDiffSecond([StartTime], Now()) |
Yes |
DateDiffTick(startDate, endDate) |
Returns the number of tick boundaries between two non-nullable dates. |
DateDiffTick([StartTime], Now()) |
- |
DateDiffYear(startDate, endDate) |
Returns the number of year boundaries between two non-nullable dates. |
DateDiffYear([StartTime], Now()) |
Yes |
GetDate(DateTime) |
Extracts a date from the defined DateTime. |
GetDate([OrderDateTime]) |
Yes |
GetDay(DateTime) |
Extracts a day from the defined DateTime. |
GetDay([OrderDate]) |
Yes |
GetDayOfWeek(DateTime) |
Extracts a day of the week from the defined DateTime. |
GetDayOfWeek([OrderDate]) |
Yes |
GetDayOfYear(DateTime) |
Extracts a day of the year from the defined DateTime. |
GetDayOfYear([OrderDate]) |
Yes |
GetHour(DateTime) |
Extracts an hour from the defined DateTime. |
GetHour([StartTime]) |
Yes |
GetMilliSecond(DateTime) |
Extracts milliseconds from the defined DateTime. |
GetMilliSecond([StartTime]) |
- |
GetMinute(DateTime) |
Extracts minutes from the defined DateTime. |
GetMinute([StartTime]) |
Yes |
GetMonth(DateTime) |
Extracts a month from the defined DateTime. |
GetMonth([StartTime]) |
Yes |
GetSecond(DateTime) |
Extracts seconds from the defined DateTime. |
GetSecond([StartTime]) |
Yes |
GetTimeOfDay(DateTime) |
Extracts the time of the day from the defined DateTime in ticks. |
GetTimeOfDay([StartTime]) |
- |
GetYear(DateTime) |
Extracts a year from the defined DateTime. |
GetYear([StartTime]) |
Yes |
IsThisMonth(DateTime) |
Returns True if the specified date falls within the current month. |
IsThisMonth([OrderDate]) |
Yes |
IsThisWeek(DateTime) |
Returns True if the specified date falls within the current week. |
IsThisWeek([OrderDate]) |
Yes |
IsThisYear(DateTime) |
Returns True if the specified date falls within the current year. |
IsThisYear([OrderDate]) |
Yes |
LocalDateTimeDayAfterTomorrow() |
Returns a date-time value corresponding to the day after Tomorrow. |
AddDays(LocalDateTimeDayAfterTomorrow(), 5) |
Yes |
LocalDateTimeLastWeek() |
Returns a date-time value corresponding to the first day of the previous week. |
AddDays(LocalDateTimeLastWeek(), 5) |
Yes |
LocalDateTimeNextMonth() |
Returns a date-time value corresponding to the first day of the next month. |
AddMonths(LocalDateTimeNextMonth(), 5) |
Yes |
LocalDateTimeNextWeek() |
Returns a date-time value corresponding to the first day of the following week. |
AddDays(LocalDateTimeNextWeek(), 5) |
Yes |
LocalDateTimeNextYear() |
Returns a date-time value corresponding to the first day of the following year. |
AddYears(LocalDateTimeNextYear(), 5) |
Yes |
LocalDateTimeNow() |
Returns a date-time value corresponding to the current moment in time. |
AddDays(LocalDateTimeNow(), 5) |
Yes |
LocalDateTimeThisMonth() |
Returns a date-time value corresponding to the first day of the current month. |
AddMonths(LocalDateTimeThisMonth(), 5) |
Yes |
LocalDateTimeThisWeek() |
Returns a date-time value corresponding to the first day of the current week. |
AddDays(LocalDateTimeThisWeek(), 5) |
Yes |
LocalDateTimeThisYear() |
Returns a date-time value corresponding to the first day of the current year. |
AddYears(LocalDateTimeThisYear(), 5) |
Yes |
LocalDateTimeToday() |
Returns a date-time value corresponding to Today. |
AddDays(LocalDateTimeToday(), 5) |
Yes |
LocalDateTimeTomorrow() |
Returns a date-time value corresponding to Tomorrow. |
AddDays(LocalDateTimeTomorrow(), 5) |
Yes |
LocalDateTimeTwoWeeksAway() |
Returns a date-time value corresponding to the first day of the week that is after next week. |
AddDays(LocalDateTimeTwoWeeksAway(), 5) |
Yes |
LocalDateTimeYesterday() |
Returns a date-time value corresponding to Yesterday. |
AddDays(LocalDateTimeYesterday(), 5) |
Yes |
Now() |
Returns the current system date and time. |
AddDays(Now(), 5) |
Yes |
Today() |
Returns the current date. Regardless of the actual time, this function returns midnight of the current date. |
AddMonths(Today(), 1) |
Yes |
UtcNow() |
Returns the current system date and time, expressed as Coordinated Universal Time (UTC). |
AddDays(UtcNow(), 7) |
- |
Logical Functions
Function |
Description |
Example |
XLS(x) Format Export-Friendly See Note |
Iif(Expression, TruePart, FalsePart) |
Returns either TruePart or FalsePart, depending on the evaluation of the Boolean Expression. |
Iif([Quantity]>=10, 10, 0 ) |
- |
IsNull(Value) |
Returns True if the specified Value is NULL. |
IsNull([OrderDate]) |
Yes |
IsNull(Value1, Value2) |
Returns Value1 if it is not set to NULL; otherwise, Value2 is returned. |
IsNull([ShipDate], [RequiredDate]) |
- |
IsNullOrEmpty(String) |
Returns True if the specified String object is NULL or an empty string; otherwise, False is returned. |
IsNullOrEmpty([ProductName]) |
Yes |
Math Functions
Function |
Description |
Example |
XLS(x) Format Export-Friendly |
Abs(Value) |
Returns the absolute, positive value of the given numeric expression. |
Abs(1 - [Discount]) |
Yes |
Acos(Value) |
Returns the arccosine of a number (the angle in radians, whose cosine is the given float expression). |
Acos([Value]) |
Yes |
Asin(Value) |
Returns the arcsine of a number (the angle in radians, whose sine is the given float expression). |
Asin([Value]) |
Yes |
Atn(Value) |
Returns the arctangent of a number (the angle in radians, whose tangent is the given float expression). |
Atn([Value]) |
Yes |
Atn2(Value1, Value2) |
Returns the angle whose tangent is the quotient of two specified numbers in radians. |
Atn2([Value1], [Value2]) |
Yes |
BigMul(Value1, Value2) |
Returns an Int64 containing the full product of two specified 32-bit numbers. |
BigMul([Amount], [Quantity]) |
- |
Ceiling(Value) |
Returns the smallest integer that is greater than or equal to the given numeric expression. |
Ceiling([Value]) |
Yes |
Cos(Value) |
Returns the cosine of the angle defined in radians. |
Cos([Value]) |
Yes |
Cosh(Value) |
Returns the hyperbolic cosine of the angle defined in radians. |
Cosh([Value]) |
Yes |
Exp(Value) |
Returns the exponential value of the given float expression. |
Exp([Value]) |
Yes |
Floor(Value) |
Returns the largest integer less than or equal to the given numeric expression. |
Floor([Value]) |
Yes |
Log(Value) |
Returns the natural logarithm of a specified number. |
Log([Value]) |
Yes |
Log(Value, Base) |
Returns the logarithm of a specified number in a specified Base. |
Log([Value], 2) |
Yes |
Log10(Value) |
Returns the base 10 logarithm of a specified number. |
Log10([Value]) |
Yes |
Max(Value1, Value2) |
Returns the maximum value from the specified values. |
Max([Value1], [Value2]) |
Yes |
Min(Value1, Value2) |
Returns the minimum value from the specified values. |
Min([Value1], [Value2]) |
Yes |
Power(Value, Power) |
Returns a specified number raised to a specified power. |
Power([Value], 3) |
Yes |
Rnd() |
Returns a random number that is less than 1, but greater than or equal to zero. |
Rnd()*100 |
Yes |
Round(Value) |
Rounds the given value to the nearest integer. |
Round([Value]) |
Yes |
Round(Value, Precision) |
Rounds the given value to the nearest integer, or to a specified number of decimal places. |
Round([Value], 2) |
Yes |
Sign(Value) |
Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. |
Sign([Value]) |
Yes |
Sin(Value) |
Returns the sine of the angle defined in radians. |
Sin([Value]) |
Yes |
Sinh(Value) |
Returns the hyperbolic sine of the angle defined in radians. |
Sinh([Value]) |
Yes |
Sqr(Value) |
Returns the square root of a given number. |
Sqr([Value]) |
- |
Tan(Value) |
Returns the tangent of the angle defined in radians. |
Tan([Value]) |
Yes |
Tanh(Value) |
Returns the hyperbolic tangent of the angle defined in radians. |
Tanh([Value]) |
Yes |
ToDecimal(Value) |
Converts Value to an equivalent decimal number. |
ToDecimal([Value]) |
- |
ToDouble(Value) |
Converts Value to an equivalent 64-bit double-precision floating-point number. |
ToDouble([Value]) |
- |
ToFloat(Value) |
Converts Value to an equivalent 32-bit single-precision floating-point number. |
ToFloat([Value]) |
- |
ToInt(Value) |
Converts Value to an equivalent 32-bit signed integer. |
ToInt([Value]) |
- |
ToLong(Value) |
Converts Value to an equivalent 64-bit signed integer. |
ToLong([Value]) |
- |
String Functions
Function |
Description |
Example |
XLS(x) Format Export-Friendly |
Ascii(String) |
Returns the ASCII code value of the leftmost character in a character expression. |
Ascii('a') |
- |
Char(Number) |
Converts an integerASCIICode to a character. |
Char(65) + Char(51) |
Yes |
CharIndex(String1, String2) |
Returns the starting position of String1 within String2, beginning from the zero character position to the end of a string. |
CharIndex('e', 'devexpress') |
- |
CharIndex(String1, String2, StartLocation) |
Returns the starting position of String1 within String2, beginning from the StartLocation character position to the end of a string. |
CharIndex('e', 'devexpress', 2) |
- |
Concat(String1, ... , StringN) |
Returns a string value containing the concatenation of the current string with any additional strings. |
Concat('A', ')', [ProductName]) |
Yes |
Contains(String1, SubString1) |
Returns True if SubString1 occurs within String1; otherwise, False is returned. |
Contains([ProductName], 'dairy') |
Yes |
EndsWith(String1, SubString1) |
Returns True if the end of String1 matches SubString1; otherwise, False is returned. |
EndsWith([Description], 'The end.') |
Yes |
Insert(String1, StartPosition, String2) |
Inserts String2 into String1 at the position specified by StartPositon |
Insert([Name], 0, 'ABC-') |
- |
Len(Value) |
Returns an integer containing either the number of characters in a string or the nominal number of bytes required to store a variable. |
Len([Description]) |
Yes |
Lower(String) |
Returns String in lowercase. |
Lower([ProductName]) |
Yes |
PadLeft(String, Length) |
Left-aligns characters in the defined string, padding its left side with white space characters up to a specified total length. |
PadLeft([Name], 30) |
- |
PadLeft(String, Length, Char) |
Left-aligns characters in the defined string, padding its left side with the specified Char up to a specified total length. |
PadLeft([Name], 30, '<') |
- |
PadRight(String, Length) |
Right-aligns characters in the defined string, padding its left side with white space characters up to a specified total length. |
PadRight([Name], 30) |
- |
PadRight(String, Length, Char) |
Right-aligns characters in the defined string, padding its left side with the specified Char up to a specified total length. |
PadRight([Name], 30, '>') |
- |
Remove(String, StartPosition) |
Deletes all characters from this instance, beginning at a specified position. |
Remove([Name], 3) |
- |
Remove(String, StartPosition, Length) |
Deletes a specified number of characters from this instance, beginning at a specified position. |
Remove([Name], 0, 3) |
- |
Replace(String, SubString2, String3) |
Returns a copy of String1, in which SubString2 has been replaced with String3. |
Replace([Name], 'The ', '') |
- |
Reverse(String) |
Reverses the order of elements within String. |
Reverse([Name]) |
- |
StartsWith(String1, SubString1) |
Returns True if the beginning of String1 matches SubString1; otherwise, False is returned. |
StartsWith([Title], 'The best') |
Yes |
Substring(String, StartPosition, Length) |
Retrieves a substring from String. The substring starts at StartPosition and has a specified Length. |
Substring([Description], 2, 3) |
- |
Substring(String, StartPosition) |
Retrieves a substring from String. The substring starts at StartPosition. |
Substring([Description], 2) |
- |
ToStr(Value) |
Returns a string representation of an object. |
ToStr([ID]) |
- |
Trim(String) |
Removes all leading and trailing SPACE characters from String. |
Trim([ProductName]) |
Yes |
Upper(String) |
Returns String in uppercase. |
Upper([ProductName]) |
Yes |
