How Can We Help?
Facet Functions
Facet Functions
Facets use functions to determine which values will be matched against each facet. A list of the available functions is below:
Note: some functions will not return true/false values, and those can only be composed together as values passed to other functions when constructing faceted queries.
Function: AndArgs
Syntax
AndArgs(arg1,arg2... argN) |
Description
AndArgs will take two or more arguments, and return true, if all the arguments are also true.
AndArgs is useful to combine the results of two or more comparison functions together.
Examples
Where Status is Draft and Type is Bug:
Faceted AndArgs(Equal(Status,Open), Equal(Type,Bug)) AS "Open Bugs" { COUNT } WHERE EntityType = Incident |
Where Type is Epic and HasCoverage is true:
Faceted AndArgs(Equal(Type,Epic), Equal(HasCoverage, Yes)) AS "Epics with Coverage" { COUNT } WHERE EntityType = Requirement |
Function: DateAdd
Syntax
DateAdd(<FieldName|Function>, <timespan>) |
Description
DateAdd allows you to add time to a date value, this can be used to modify values passed to the comparison functions such as GreaterThan
The TimeSpan can be in one of the following formats:
- hh:mm:ss i.e. 5 minutes would be “00:05:00”
- N w(eeks), N d(ays), N h(ours), N m(inutes) i.e. “2 weeks”, “2d 4h”, “5 minutes”, “5m” are all valid values.
Examples
Everything where the item was created more than 2 weeks before its last update:
Faceted GreaterThan(LastUpdatedAt, DateAdd(CreatedAt, 2w)) { COUNT } WHERE EntityType = Script |
Function: DateSubtract
Syntax
DateSubtract(<FieldName|Function>, <timespan>) |
Description
DateSubtract allows you to add time to a date value, this can be used to modify values passed to the comparison functions such as GreaterThan
The TimeSpan can be in one of the following formats:
- hh:mm:ss i.e. 5 minutes would be “00:05:00”
- N w(eeks), N d(ays), N h(ours), N m(inutes) i.e. “2 weeks”, “2d 4h”, “5 minutes”, “5m” are all valid values.
Examples
Everything where the item was created more then 2 weeks before its last update:
Faceted LessThan(CreatedAt, DateSubtract(LastUpdatedAt, 2w)) { COUNT } WHERE EntityType = Script |
Function: Equal
Syntax
Equal(<FieldName|Function>, <Value|Function>) |
Description
Equal allows you to compare the value of a field or function to a fixed value, another field or a function.
It will return true if the first parameter is equal to the second parameter.
Examples
Priority high:
Faceted Equal(Priority,High) { COUNT } WHERE EntityType = Requirement |
EstimatedDuration is equal to 5 minutes:
Faceted Equal(EstimatedDuration, '5 minutes') { COUNT } WHERE EntityType = Script |
CreatedAt date/time is equal to the LastUpdatedAt date/time:
Function: Field
Syntax
Field(fieldName) |
Description
Field takes a single string argument and returns the value of the field. This function is normally implicitly declared when using comparison functions such as Equal, so for example this:
Equal(Priority,High) |
Automatically becomes this during query compilation:
Equal(Field(Priority), High) |
Field is useful when passing values to non-comparison functions, such as DateAdd, DateSubtract etc. or when comparing one field to another.
Examples
Count of items where the DueDate (custom date field) is after the last updated date:
Faceted GreaterThan(DueDate, Field(LastUpdatedAt)) AS "Changed after due" { COUNT } WHERE EntityType = Incident |
Function: GreaterThan
Syntax
GreaterThan(<FieldName|Function>, <Value|Function>) |
Description
Greater than allows you to compare the value of a field or function to a fixed value, another field or a function.
It will return true if the first parameter is greater than the second parameter.
Examples
Average duration of scripts with more than 10 steps:
Faceted GreaterThan(ScriptStepCount,10) { Average(EstimatedDuration) } WHERE EntityType = Script |
EstimatedDuration is greater than 5 minutes:
Faceted GreaterThan(EstimatedDuration, '5 minutes') { COUNT } WHERE EntityType = Script |
Script assignments where the last run has an actual duration that is greater than the estimated duration for the associated script:
Faceted GreaterThan(ActualDuration, Field(EstimatedDuration)) { COUNT } WHERE EntityType = ScriptAssignment |
Function: GreaterThanOrEqual
Syntax
GreaterThanOrEqual(<FieldName|Function>, <Value|Function>) |
Description
The GreaterThanOrEqual function allows you to compare the value of a field or function to a fixed value, another field or a function.
It will return true if the first parameter is greater than or equal in value to the second parameter.
Examples
Average duration of scripts with 10 or more steps:
Faceted GreaterThanOrEqual(ScriptStepCount,10) { Average(EstimatedDuration) } WHERE EntityType = Script |
EstimatedDuration is greater than or equal to 10 minutes:
Faceted GreaterThanOrEqual(EstimatedDuration, '10 minutes') { COUNT } WHERE EntityType = Script |
Script assignments where the last run has an actual duration that is equal to or greater to the estimated duration for the associated script:
Faceted GreaterThanOrEqual(ActualDuration, Field(EstimatedDuration)) { COUNT } WHERE EntityType = ScriptAssignment |
Function: IsNot
Syntax
IsNot(function) |
Description
The IsNot function takes a single parameter, which should be a function that returns a boolean (true/false) value. If the function returns true, IsNot will return false and if the return value is false, IsNot will return true.
Examples
Any scripts where the step count is NOT between 10 and 20:
Faceted IsNot(Range(ScriptStepCount,10,20)) { COUNT } WHERE EntityType = Script |
Function: LessThan
Syntax
LessThan(<FieldName|Function>, <Value|Function>) |
Description
The LessThan function allows you to compare the value of a field or function to a fixed value, another field or a function.
It will return true if the first parameter is less than the second parameter.
Examples
Average duration of scripts with less than 20 steps:
Faceted LessThan(ScriptStepCount,20) { Average(EstimatedDuration) } WHERE EntityType = Script |
EstimatedDuration is less than 5 minutes:
Faceted LessThan(EstimatedDuration, '5 minutes') { COUNT } WHERE EntityType = Script |
Script assignments where the last run has an actual duration that is less than the estimated duration for the associated script:
Faceted LessThan(ActualDuration, Field(EstimatedDuration)) { COUNT } WHERE EntityType = ScriptAssignment |
Function: LessThanOrEqual
Syntax
LessThanOrEqual(<FieldName|Function>, <Value|Function>) |
Description
The LessThanOrEqual function allows you to compare the value of a field or function to a fixed value, another field or a function.
It will return true if the first parameter is less than or equal to the second parameter.
Examples
Average duration of scripts with less than or equal to 30 steps:
Faceted LessThanOrEqual(ScriptStepCount,30) { Average(EstimatedDuration) } WHERE EntityType = Script |
EstimatedDuration is less than or equal to 5 minutes:
Faceted LessThanOrEqual(EstimatedDuration, '5 minutes') { COUNT } WHERE EntityType = Script |
Script assignments where the last run has an actual duration that is less than or equal to the estimated duration for the associated script:
Faceted LessThanOrEqual(ActualDuration, Field(EstimatedDuration)) { COUNT } WHERE EntityType = ScriptAssignment |
Function: NotEqual
Syntax
NotEqual(<FieldName|Function>, <Value|Function>) |
Description
Not Equal allows you to compare the value of a field or function to a fixed value, another field or a function.
It will return true if the first parameter is not equal to the second parameter.
Examples
Priority is not low:
Faceted NotEqual(Priority,Low) { COUNT } WHERE EntityType = Requirement |
EstimatedDuration is not equal to 15 minutes:
Faceted NotEqual(EstimatedDuration,'15 minutes') { COUNT } WHERE EntityType = TestScript |
CreatedAt date/time is not equal to the LastUpdatedAt date/time:
Faceted NotEqual(CreatedAt, Field(LastUpdatedAt)) { COUNT } WHERE EntityType = TestScript |
Function: Now
Syntax
Now() |
Description
Now returns the current date and time.
Examples
All scripts where the script was created more than 2 weeks before today:
Faceted LessThan(CreatedAt, DateSubtract(Now(), 2w)) { COUNT } WHERE EntityType = Script |
Function: OrArgs
Syntax
OrArgs(arg1,arg2... argN) |
Description
OrArgs will take two or more arguments, and return true, if any of the arguments passed to it are true.
OrArgs is useful to combine the results of two or more comparison functions together.
Examples
Where Priority is High, or the Custom Field ‘Severity’ is High:
Faceted OrArgs(Equal(Priority,High), Equal(Severity,High)) AS "Urgent" { COUNT } WHERE EntityType = Incident |
Where Type is ‘Non Functional’ or item has no coverage:
Faceted OrArgs(Equal(Type,'Non Functional'), Equal(HasCoverage,No)) AS "Non Function or Uncovered" { COUNT } WHERE EntityType = Requirement |
Function: Range
Syntax
Range(<FieldName|Function>, start, end) |
Description
Range allows you to compare a field or the result of a function to a start and end value.
It will return true if the first parameter’s value falls within the start and end values of the range.
Examples
Average estimated duration of scripts with between 10 and 20 steps:
Faceted Range(ScriptStepCount,10,20) { Average(EstimatedDuration) } WHERE EntityType = Script |
Count of steps with an EstimatedDuration between 10 and 30 minutes:
Faceted Range(EstimatedDuration, '10m','30m') { COUNT } WHERE EntityType = Script |
Function: Today
Syntax
Today() |
Description
Today returns the todays date with no time component (i.e. the very start of today)
Examples
All scripts that were updated today:
Faceted GreaterThanOrEqual(LastUpdatedAt, Today()) { COUNT } WHERE EntityType = Script |
Function: Unmatched
Syntax
Unmatched() |
Description
Unmatched is a special function which returns true if the value has not matched any of the previous facets, it does not accept any arguments.
To illustrate – if there were 3 requirements, each with different priorities:
- Requirement A – Priority: High
- Requirement B – Priority: Low
- Requirement C – Priority: Medium
We could write a faceted aggregation expression:
Faceted Equal(Priority, High) AS 'Urgent', OrArgs(Equal(Priority, Low), Equal(Priority, Medium)) AS 'Non-Urgent'{ COUNT}WHERE EntityType = Requirement |
But if we add more non-high priorities to the picklist over time, we will need to update our faceted query to then capture those values.
As an alternative we could just write this:
Faceted Equal(Priority,High) as 'Urgent', Unmatched() as 'Non-Urgent'{ COUNT}WHERE EntityType = Requirement |
In this case, anything that doesn’t match urgent will be “unmatched” and fall through to the “Unmatched()” facet.
Function: Yesterday
Syntax
Yesterday()
Description
Yesterday returns the previous day’s date with no time component.
Examples
All scripts where the script was created before yesterday:
Faceted LessThan(CreatedAt, Yesterday()) { COUNT } WHERE EntityType = Script