Page Comparison

Returns the average of a list of numbers.

AVG(NumberOutOfGrid, Grid.Number)

Returns the average of all numbers in a range of values, based on a given criteria.

AVG(">10", NumberOutOfGrid, Grid.Number) - will return the average of the numbers bigger than 10

Returns true if given value is between two numbers.

Between(Number, 0, 10) - If set as Visualization rule, the number will be visible only if field Number is between 0 and 10, including

Use this function to evaluate selected items in a pick list or multi-select field. For example, CONTAIN(PickList, "Pass").

If you want your fields to be visible when two options are selected(available only in Multilist) you can combine two CONTAIN function, or any other:

CONTAIN(MultiList, "Option 1") AND CONTAIN(MultiList, "Option 2") OR CONTAIN(MultiList, "Option 3")

Every possible logical operators can be applied and if you want to change the priority just add parenthesis where necessary:

CONTAIN(MultiList, "Option 1") AND (CONTAIN(MultiList, "Option 2") OR CONTAIN(MultiList, "Option 3"))

Returns the number of arguments.

COUNT(Grid.Number) - will return the number of rows in the grid, where Number field has value

Returns the number of arguments that are empty.

COUNTBLANK(Grid.Number) - will return the number of rows in the grid, where Number field has no value

Returns the number of arguments that meet a certain criteria.

COUNTIF(">10", Grid.Number) - will return the number of rows in the grid, where Number field is bigger than 10

Takes the prior record Cume value and adds the current record "Field". Only to be used in grid rows.

CUME(Number) - That set to field will cause in first row the field to be equal of the value for Number. In any other rows will be the sum of the field in prior row + the value of Number in current row.

Returns plain text as date. Value must be in format "MM/DD/YYYY".

Returns a specified date with the specified number interval added to a specified date part of that date.

DATEADD(Date, 5) - If we set for Date 31 May 2016, it will return 05 June 2016

Returns the count of the specified date part boundaries crossed between dates.

Units:

1)  DATEDIFF ( FirstDate, SecondDate, "days" ) - best to be set in TextBox or Number field. If First Date is 12/04/2015 and Second Date 12/01/2015 the result will be 3. Keep in mind that if we reverse the values First Date is 12/01/2015 and Second Date 12/04/2015 the result will be -3.

2) DATEDIFF ( FirstDate, SecondDate, "hours" ) - best to be set in TextBox field. If First Date is 12/04/2015 and Second Date 12/01/2015 the result will be 72:00. Keep in mind that if we reverse the values First Date is 12/01/2015 and Second Date 12/04/2015 the result will be -72:00.

Returns the difference between two DateTime values in specific format.

Let set values this values for the example:

FirstDate - 12/04/2015

FirstTime - 16:10

SecondDate - 01/04/2016

SecondTime -16:00

Format :

1) DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "YY:MM:DD:HH:MM" ) - The result will be 00:01:00:23:50, best to be set in TextBox field

2)  DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "MM:DD:HH:MM" ) - The result will be 01:00:23:50, best to be set in TextBox field

3)  DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "DD:HH:MM" ) - The result will be 30:23:50, best to be set in TextBox field

4)  DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "HH:MM" ) - The result will be 743:50, best to be set in TextBox field

5)  DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "MM" ) - The result will be 44630, best to be set in TextBox or Number field

6)  DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "Months" ) - The result will be 1 Months, 0 Weeks, 0 Days, 23 Hours, 50 Minutes, best to be set in TextBox field

7)  DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "Weeks" ) - The result will be 4 Weeks, 2 Days, 23 Hours, 50 Minutes, best to be set in TextBox field

8)  DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "Days" ) - The result will be 30 Days, 23 Hours, 50 Minutes, best to be set in TextBox field

9)  DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "Hours" ) - The result will be 743 Hours, 50 Minutes, best to be set in TextBox field

10)  DATETIMEDIFF ( FirstDate, FirstTime , SecondDate , SecondTime , "Minutes" ) - The result will be 44630 Minutes, best to be set in TextBox field

Returns a specified date with the specified number interval subtracted from a specified date part of that date.

DATEADD(Date, 5) - If we set for Date 31 May 2016, it will return 26 May 2016

Determine Peak Intersection On Plotted Curve.

XValues: Number field in grid

YValues: Number field in grid

Axis: 1) "XAxis" 2) "YAxis"

ExcludeFlags : Optional Yes/No Field in grid. If selected Yes, the function will skip X and Y values for that grid row.

DETERMINEPEAK(Grid.XValues, Grid.YValues, "XAxis", Grid.Exclude) - Best set in Number field with Decimal places setting

Returns "true" if the selected field has value and "false" if there is no value.

HASVALUE(Date) - set as Visualization Rule will show the field only if Date has value

Use the If / Then / Else function to conditionally evaluate data conditions and perform various calculations based on that data condition.

For example, If(Result1 > 10, "Success", "Fail"). The Else value is optional and if you skip it and the condition is not satisfied no value will be assigned to field - If(Result1 > 10, "Success").

Returns the length of the specified string.

LENGTH(TextArea) - will return number of characters in the field.

Return the largest number from a list of numbers.

MAX(Grid.Number, NumberOutOfGrid) - will return the max number from all grid rows and number field out of grid, in this example

Returns the smallest number from a list of numbers.

MIN(Grid.Number, NumberOutOfGrid) - will return the min number from all grid rows and number field out of grid, in this example

Returns number PI round by precision.

Precision: Decimal number with max value of 15

PI(5) = 3.14159, best set in Number field with decimal places, or TextBox field for precision bigger than 7

Returns a number by a chosen power. Static numbers can be typed in or other fields can be referenced (if they are numeric fields). Powers must be integers.

POWER(Number, 3) - if number is 3 will return 27

Returns the field in prior data grid row.

Can be used for grid row numbers PRIOR(RowNumber) + 1, once set in first row as 1 will increase on each next by 1.

Returns a number rounded to a specified number of digits..

Method:

1) "STANDARD" - If no Precision is entered the default behavior will be rounding to the nearest integer - ROUND(4.5) = 5, ROUND(4.44456) = 4. If you want rounding to second digit after the decimal separator, include the optional precision argument. For example, ROUND(4.44456, 2) = 4.44.

2) "EVEN" - Precision must be 0. Return the value rounded to the nearest even number.

ROUND(7.01, 0, "EVEN") = 8, ROUND(6.99, 0, "EVEN") = 6

3) "TEN" - Precision must be 0. Return the value rounded to the nearest 10.

ROUND(15.01, 0, "TEN") = 20, ROUND(14.99, 0, "TEN") = 10

4) "DOT05" - Precision must be 0. Return the value rounded to the nearest 0.05.

ROUND(5.12, 0, "DOT05") = 5.1, ROUND(5.13, 0, "DOT05") = 5.15

5) "DOT25" - Precision must be 0. Return the value rounded to the nearest 0.25.

ROUND(5.12, 0, "DOT25") = 5, ROUND(5.13, 0, "DOT25") = 5.25

6) "DOT5" - Precision must be 0. Return the value rounded to the nearest 0.5.

ROUND(5.24, 0, "DOT5") = 5, ROUND(5.26, 0, "DOT5") = 5.5

If no method is selected the function applies "Standard" method.

Returns the square root of a number or chosen numeric field.

SQRT(16) = 4

Select option(s) for chosen field.

Have PickList with options: OptionA, OptionB and Number field.

SELECTOPTIONS(PickList, "OptionA", Number < 10, "OptionB", Number > 10) will select OptionA if we set number with lower value than 10, OptionB if value is over 10 and nothing if the value is 10(no such condition).

Returns the sum of a list of numbers.

SUM(Grid.Number, Grid.SecondNumber)

Returns the sum of any given numbers that meet a certain criteria.

SUM(">10", Grid.Number) will sum only values from rows in which Number field has Value bigger than 10.

Returns the difference between two time values.

Rounding:

1) TIMEDIFF(Time1, Time2, "Closest15")

If Time1 = 15:00 and Time2 = 15:22 the result will be 00:15 because 22 is closer to 15 than to 30, if Time1 is still 15:00 but Time2 is 15:24 the result will be 00:30

2) TIMEDIFF(Time1, Time2, "Upper15")

If Time1 = 15:00 and Time2 = 15:22 the result will be 00:30 because first upper quarter over 22 is 30, if Time1 is still 15:00 but Time2 is 15:24 the result still will be 00:30

3) TIMEDIFF(Time1, Time2, "Closest30")

If Time1 = 15:00 and Time2 = 15:14 the result will be 00:00 because 14 is closer to 00 than to 30, if Time1 is still 15:00 but Time2 is 15:15 the result will be 00:30

4) TIMEDIFF(Time1, Time2, "Upper30")

If Time1 = 15:00 and Time2 = 15:14 the result will be 00:30 because first upper half over 14 is 30, if Time1 is still 15:00 but Time2 is 15:24 the result still will be 00:30

5) TIMEDIFF(Time1, Time2, "Closest60")

If Time1 = 15:00 and Time2 = 15:29 the result will be 00:00 because 29 is closer to 00 than to 60, if Time1 is still 15:00 but Time2 is 15:30 the result will be 01:00

6) TIMEDIFF(Time1, Time2, "Upper60")

If Time1 = 15:00 and Time2 = 15:29 the result will be 01:00 because first upper hour over 29 is 60, if Time1 is still 15:00 but Time2 is 15:30 the result still will be 01:00