fbpx

Facet Functions

< Back

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