dateformatnoshift takes a date field and converts it into a grouping without appylying any sort of time zone shift:
dateformatnoshift([Field], "yyyy-MM-dd")
would create a grouping of 2022-06-15, while
parsedate([Field], "dd-MM-yyyy")
would create a grouping of 15-06-2022.
directreplace performs text replacement on the specified parameters:
directreplace([Field], "ABC", "zzz")
would replace A312 in an example of Code ABC Test with Code zzz test.
firstvalue returns the first value in the list of parameters containing a value:
firstvalue([Invoice Number], [Credit Note Number])
would return Invoice Number if present, and otherwise retrieve Credit Note Number. This is particularly useful when you're using a first value-style report/
indexof returns the index of a specified character.
indexof([Field], "Z")
would return a zero-offset value of 2 on a row containing a value in Field of XYZ.
json parses a field containing JSON using a JSON Path expression.
json([Field], "$.name")
would return a value of the name field found on the JSON structure in the data.
lastindexof returns the last index of a specified character.
lastindexof([Field], "Z")
would return a zero-offset value of 2 on a row containing a value in Field of Z Z.
lower returns a value in lower case.
lower([Field])
parsedate takes a grouping field and converts it into a date:
parsedate([Field], "yyyy-MM-dd")
would create a date out of a field containing 2022-06-15.
parsedate([Field], "dd-MM-yyyy")
would create a date out of a field containing 15-06-2022.
regex uses regular expression matching to parse text.
regex([Field], "A([0-9])*", 0)
would return A312 in an example of Code A312 Test.
replace performs text replacement using regular expressions:
replace([Field], "A([0-9])*", "zzz")
would replace A312 in an example of Code A312 Test with Code zzz test.
split breaks the selected text value apart by a delimiter and returns the selected part.
split([Field], "|", 1)
on a value of A|B|C would return B.
stringliteral returns the specified text. For example:
stringliteral("")
will simply return an empty cell for you.
stringliteral("Placeholder Text")
will return the value of Placeholder Text.
striphtml removes any HTML tag fields from the value.
striphtml([Field])
on a value of:
XY<br>Z
would return:
XYZ
substring returns the subset of characters between the specified start and end indices.
substring([Field], 5, 10)
returns the characters starting at the 5th letter and ending at the 10th letter of the value in Field.
substring([Field], 5)
returns the characters starting at the 5th letter of the value in Field.
tail returns the last N characters of the specified text value.
tail([Field], 3)
on a value of ABC DEF would return DEF.
trim removes any leading or trailing whitespace.
trim([Field])
on a value of XYZ would return XYZ.
uniqueconcat creates a comma separated list of values. For example, you might have multiple assignees possible per task, but want to see all assignees in a single table cell. To do this, you could use a calculation of:
uniqueconcat([Task ID], [Assignee Name])
For each unique task ID, it will combine the set of matching Assignee Names so that you'll get "Jim, Anna" or "Bob, Mike, Nancy" as values per task.
uniqueconcat creates a list of values separated by the specified delimiter. For example, you might have multiple assignees possible per task, but want to see all assignees in a single table cell separated by a pipe. To do this, you could use a calculation of:
uniqueconcatwithdelimier([Task ID], [Assignee Name], "|")
For each unique task ID, it will combine the set of matching Assignee Names so that you'll get "Jim|Anna" or "Bob|Mike|Nancy" as values per task.
upper returns a value in upper case.
upper([Field])
