The minQL formula language gives you full access to your data in minware. You can add minQL formulas for all custom report fields and dashboard filters.

minQL offers the power of SQL, but with the simplicity of spreadsheet formulas. It lets you quickly create reports using time-series data from tickets, commits, pull requests, and more.

This page documents all of the core objects and methods available in minQL, along with examples and links to detailed reference documentation for all of the available object properties and functions. It is meant to be read from start to finish, and is a good place to start if you are new to minQL.

This page includes some example minQL formulas, and you can find more examples in the custom report itself by adding any of the default values and then editing the formula. You can also look at the formulas used in reports in the Report Library. All default and report library values are written in minQL, so you can see precisely how they are defined and copy them to create your own formulas.

Where You Can Use minQL

minQL is available in the custom report and custom dashboard filters by selecting the minQL formula option. Here are the places where you can use minQL, along with the type of expression allowed:

minQL Formula Location	Supported Expression(s)
Custom Report Chart X-Axis (or Y-Axis if horizontal)	Property, Metric
Custom Report Table Metrics/Chart Y-Axis (or X-Axis if horizontal)	Metric
Custom Report Breakdown	Property
Custom Report and Dashboard Filters	Property

Property and Metric Expression Types

In minQL, there are two types of expressions: property expressions and metric expressions. Metric expressions specify a source of data rows and compute an aggregate value over those rows. Property expressions compute a value using properties from a single data row.

For example, “Tickets Completed” or “Dev Work Time” is a metric, while “Ticket Summary” or “Assignee Team” is a property.

Custom reports must include at least one metric to provide a source of data rows.

You can add as many property expressions in the custom report breakdown section as you’d like. These values will produce additional chart columns and split data into different series along the main graph axis.

Finally, you can add custom minQL filters using property expressions. When you add a minQL filter, the report will load all of the values from the formula, which you can then select to apply the filter.

Primary Object Tables

Both property and metric expressions reference primary object tables. Under the hood, each of these tables contains properties for objects of that type. The tables also store a full time-series change history for each property.

Here are the primary object tables available in minware currently, with a brief overview of their contents. For more information about these tables and their properties, see the minQL object table reference.

ticket - An “issue” or “ticket” from Jira or another ticket source. This table includes all ticket types (stories, bugs, etc.) and levels (subtasks, tasks, epics, initiatives, etc.) It also has all of the default and custom fields defined in your ticketing system.
person - This table represents people in your organization who have records in your ticketing system and/or version control system. All fields in other objects that refer to people will link to objects in this table. This table also includes data for minware’s work time allocation model.
branch - Each object in this table is a sequence of one or more code commits from your version control system. Branch objects do not exactly correspond to named branches in your version control system because commits will be in exactly one canonical branch object and branch objects will have at most one merge/pull request. So, if a branch name is merged multiple times, then it may correspond to multiple minQL branch objects, and direct main branch commits are grouped into separate branch objects for each author and commit. Branches are linked to tickets based on a ticket ID in the branch name or in a pull request title or description.
team - This table holds information about teams of people, including the team name and parent hierarchy.
sprint - This table holds information about sprints, including their status, current tickets, name, board, and start/end dates.
pipeline - This table holds information about pipelines and workflows which execute as part of your CI/CD process in systems such as GitHub and GitLab.
run - This table holds information about instances of pipeline executions.
job - This table holds information about the jobs/steps/stages which make up pipeline runs.
commit - Each object in this table corresponds to a commit from the source control system.
meeting - Each object in this table holds information about a meeting or other event on a calendar.
repo - Information about the repositories in the source control system is held in this table.
time - This is a special pseudo-table available only in property expressions. It provides access to properties related to time (e.g., day, month, etc.)

Basic Syntax

minQL uses the jsep Javascript expression parser, so whitespace anywhere outside of symbols and strings doesn’t matter, and strings can be either single- or double-quoted without any effect on the result. Strings can have the same escape characters used in Javascript.

minQL supports basic operators like +, -, &&, ||, etc. from Javascript, as well as array literals (e.g., [1, 2]) and ternary operators (<test> ? <trueValue> : <falseValue>). Note that null values on either side of an operator will cause the result to be null like in SQL, other than === and !===, which treat null as a value and will always return true or false.

As you type minQL formulas, the formula entry window tells you if there are errors in your expression.

Functions and Dot Notation

minQL expressions support a variety of functions. Regular functions can be used in either the normal syntax like function(a, b), or the dot notation syntax like a.function(b). When you use the dot syntax, minQL will pass the left-hand expression preceding the dot as the first argument to the function. You can also use dot notation with numbers by wrapping them in parentheses, so (1).function(b) is equivalent to function(1, b).

Method functions that operate on objects can only be used in the dot syntax.

In the reference documentation, regular non-method functions are documented with the normal syntax, though examples may use either syntax for readability.

Property Expressions

This section covers the basics of writing property expressions in minQL. Property expressions operate on a single row of data at a time and produce a single value result.

With property expressions, you can get started adding a custom graph series, chart column, or filter to your reports.

Default Scope

In a property expression, the default set of variables in scope (available for use) consists of the primary object tables, as well as all of the functions described below.

You can reference any of the primary object tables, and minQL will include it using the default join path from the base object of each metric expression. For example, if you reference person for a “tickets created” metric, then minQL will get the person from the ticket assignee field by default. (We cover how to override the default join paths later).

Method Scopes

While arguments to regular functions (described below) support the default scope, certain object method functions support property expressions as arguments, but have different variables in scope.

Some method arguments do not allow any sub-expressions and must be literal values.

The availability of sub-expressions and the set of in-scope variables for method arguments is described in the documentation for each method.

Object Table Property Lookup Methods

The simplest property expression is looking up a property of an object. This example gets the assignee associated with a ticket:

ticket.get('assignee')

You can reference object properties using get() method functions on object tables with the dot syntax shown above. You then provide the name of the property in a string (sub-expressions to compute the property name are not allowed). For a full list of the available properties on each object, see the minQL object table reference.

There are four different lookup functions you can use: get(), getOrig(), getId(), getOrigId().

The Orig varieties of these functions will look up the original property at the time associated with the row instead of the default, which is looking up the current value. This can be used for things like getting the active ticket status when looking at development time by ticket.

Warning: getting the original value can add substantial performance overhead, so only use this if you need it.

The Id varieties of these functions retrieve the underlying ID of object properties rather than the name. (For a Jira ticket, the name is the key, e.g., DEV-123.) Referencing IDs is usually unnecessary except in advanced scenarios because nested lookups (described below) automatically use the ID instead of the name for get().

Nested Property Lookups

minQL makes it easy to look up nested properties referencing other objects. For example, you can look up the assignee associated with a branch as follows:

branch.get('ticket').get('assignee')

As mentioned above, the first get() in this example is automatically translated to getId() since it is used for an object lookup.

You can chain as many property lookups together as you’d like.

Literals

In addition to looking up values on the primary object tables in the default scope, you can use literal string, number, and boolean (true/false) values in minQL property expressions. This isn’t very useful on its own, but this is a valid property expression:

'Hello World'

For strings, you can use double or single quotes, and quotes can be escaped with backslashes.

Basic Functions

minQL also offers a full set of scalar functions (i.e., not a table, system, or aggregate function) from Snowflake in property expressions.

This example shows how to fall back on the ticket creator if there is no assignee using coalesce:

coalesce(
  ticket.get('assignee'),
  ticket.get('creator')
)

Property expressions can be arbitrarily nested with each other and with literal and object lookups to create more complex expressions.

Each of the functions are converted to lowerCamelCase, so, for example, REGEXP_SUBSTR would be regexSubstr in minQL.

Additionally, certain database expressions that don’t use a functional syntax in SQL are implemented with a functional syntax in minQL. For example, in SQL, you would say value IS NOT NULL, whereas the equivalent minQL is isNotNull(value).

Basic operators in minQL like +, -, etc. are translated to the same operators in SQL, but certain operators that don't exist in SQL are translated to SQL function calls, like the ternary <test> ? <trueValue> : <falseValue> translates to iff(<test>, <trueValue>, <falseValue>). You must use concat() to concatenate strings, as the + operator only works with numbers and there is no string concatenation operator in minQL.

For a full list of available functions, see the minQL Function Reference.

Warning: minQL does not currently validate argument types or counts for most functions, so you can write a minQL expression that shows up as valid in the user interface and end up getting a server error.

List Functions

minQL supports several list iteration functions that are not available in Snowflake.

This example shows how to filter labels that start with a particular substring:

ticket.get('labels').filterList(currentValue.like('bug-%'))

The first argument to each list function (or the argument to the left of the dot as shown here) should be a list. The second argument is an expression with the default scope, plus the following additional scope variables that you can use in list iteration:

currentValue - The current item in the list
currentIndex - The index of the current item in the list
currentList - The whole list object you are iterating over

For a full list of available list functions, see the minQL Function Reference.

The flatten() Function

In addition to the scalar functions, minQL currently supports one table function that can affect the number of rows: flatten().

You can pass a list into the flatten function, and minQL will split apart the list, duplicating or omitting entire data rows including their values. For example, this expression will split data by labels, duplicating data for multiple labels, and omitting data for tickets with no labels:

flatten(ticket.get('labels'))

The flatten function works in a special way for filters. When it is the outermost function for custom filter minQL values, flatten will not affect row counts in the report, but will instead provide the individual items as filter options so that you can include or exclude rows that contain or do not contain the values you select in the filter.

Warning: Flatten will duplicate data for each item in a list and will omit data for rows where the list is empty or null. So, including flatten in a property expression will affect the results of metric expressions.

Warning #2: Each expression that includes flatten will split rows, even if you reference the same property. So, including something like ticket.get('labels').flatten() in two expressions will split the rows twice, which is probably not what you want. The exception to this is for filter values that include flatten as the last operation, as described above.

Metric Expressions

This section talks about how to write minQL metric expressions. Metric expressions produce rows from an original object table and compute aggregate values to generate a numeric result.

Default Scope

The default scope for metric expressions includes all of the primary object tables (excluding time, as described in the primary object table section).

For metric expressions, the functions in property expressions are not available in the default scope because metric expressions operate on streams of multiple rows, so they need to start by referencing a source of rows and not a property lookup.

However, some methods in metric expressions do support property sub-expressions as arguments, which include those regular functions that operate on individual rows.

Additionally, there is one table function in the outer scope for metric expressions: merge(). The merge function combines multiple row streams and is described in more detail below.

Row Generation Methods

Starting from one of the primary object tables, the first step in writing a metric expression is calling a method that generates a stream of rows in a RowStream object.

The RowStream is a type of object in minQL that represents a sequence of rows, similar to a database table or single sheet within a spreadsheet document.

This example shows how to produce a count of created tickets:

ticket.event('created')

The event() method uses the dot syntax and only allows a string literal argument (no sub-expressions). It simply generates one row without a value for each event at the time of the event.

By default, minQL will then compute the count of events, broken down by any other breakdown or X-Axis values.

For a full list of available events, see the minQL object table reference.

minQL also offers an onChange() and an onChangeOrCreate() method to produce events when property values change. The onChangeOrCreate() method will include a row when the object is created for the first value, while onChange() will only generate rows when values change after object creation.

For example, this expression gets ticket status changes:

ticket.onChange('status')

Finally, you can generate rows with time intervals rather than point-in-time events using the duration() method. This example generates durations while a ticket was in each status:

ticket.duration('status')

Unlike other row generation methods, the default for duration() is to compute the aggregate sum of row durations in calendar days rather than count the number of rows.

Generated Row Scope Variables

The row generation methods listed in the previous section also add variables to the scope of the join(), filter(), and value() methods described next that operate on the row stream. Here are the variables added by each of the row generation methods:

event()
- subType - An optional property associated with the event. See the table reference for details on subtypes for each event type.
- idSubType - If the subtype is an object reference, this gets the object ID instead of the display value.
duration()
- value - The current value of the referenced property during the duration.
- idValue - If the value is an object reference, this gets the object ID instead of the display value.
onChange(), onChangeOrCreate()
- oldValue - The previous value of the referenced property right before the change, which will be null for the first event caused by creation for onChangeOrCreate().
- oldIdValue - If the oldValue is an object reference, this gets the object ID instead of the display value.
- value - The current value of the referenced property during the duration.
- idValue - If the value is an object reference, this gets the object ID instead of the display value.

Initial RowStream Methods

This section describes all of the methods you can initially use on RowStream objects. The output of the methods in this section is another updated RowStream object. We will cover finalization functions later that convert a RowStream to an AggregateRowStream for further post-aggregation logic.

All of the methods in this section can be called in any order, and may reference variables added to the scope by previous calls (e.g., a join() call after value() may reference the created value). Multiple calls to filter() will combine together with a logical and so that all of them must be true.

You can also call any of the methods here after merging row streams or running a state machine to further join, filter, and set values.

RowStream join() Method

After you have generated a stream of rows by calling a row generation method on a primary object table, you can link to other object types with join().

You can use the join method to override the default way that minQL joins different object types, and you can chain multiple joins together.

For example, if you want later references to person to use the ticket creator instead of the assignee (the default), you would write:

ticket.event('created')
  .join(person, ticket.getId('creator'))

Note that you don’t also need to override other objects when you specify a join because the defaults use whatever objects are present rather than a path from the root object. In the above example, references to team in value expressions will use the team of the creator instead of the assignee without you having to add a join for team.

The join method takes two arguments, each of which has a different scope:

Object Type - The type of object to join to. This must be one of the primary object tables. No sub-expressions are allowed.
Object ID Expression - This is a sub-expression with the full set of functions available. It should produce the ID of an object, and get() will not automatically be renamed to getId(), so you must use getId(). Unlike full property expressions, only the object table that is the base of the row generation method will be in scope initially and others are not added with the default path. If you chain multiple join methods together, then previously joined tables will also be available. In the example above, you could append join(team, person.get('board')) because person was added by the previous join. Variables described in the Generated Row Scope Variables section are also available.

RowStream filter() Method

The next method that is available on row streams is filter(). The filter method takes a boolean expression as an input, and omits rows where the value is false.

This example counts created tickets where the issue type is 'Story':

ticket.event('created').filter(
  ticket.get('issuetype').in('Story')
)

This next example uses the value scope variable provided by duration() to get time durations where a ticket is in progress:

ticket.duration('statusCategory').filter(
  value.in('In Progress')
)

The argument for filter is as follows:

Filter Expression - A sub-expression with the full set of functions available that evaluates to a boolean value. Only the base object table and object tables that have been added with a previous join() will be in scope. Variables described in the Generated Row Scope Variables section are also available.

Tip: Remember the difference between get() and getOrig() here. The above example will filter based on the current issue type. If you want to filter based on the issue type at time of creation, then you would need to use getOrig().

If you call filter multiple times, the conditions will be merged together with and so that only rows where all the conditions are true will pass through the filter.

RowStream value() Method

You can add a value to each row in a stream of rows with the value() method. This lets you compute results other than row counts.

The following example adds the initial story points at creation time as the row value for created tickets:

ticket.event('created').value(
  ticket.getOrig('story points')
)

Now, instead of the metric calculating a ticket count, it will calculate a sum of story points. You can also use aggregate functions other than sum, such as computing the average story points of created tickets.

The arguments for value are as follows:

Value Expression - A sub-expression with the full set of functions available that evaluates to a numeric value. Only the base object table and object tables that have been added with a previous join() will be in scope. Variables described in the Generated Row Scope Variables section are also available.
Variable Name - (Optional, defaults to 'result') This parameter must be a string literal starting with a lower-case letter and be alpha-numeric. If you specify this parameter, then the value produced by this function will be set as another variable instead of result, which will be available in the scope of later methods at the name you specify. This can be helpful if you want to compute a value without overriding the current contents of result. For example, if you use value(..., 'otherValue'), then you can reference otherValue in later methods, like filter(otherValue.eq(1)).

You can call value() multiple times, and later calls will overwrite earlier results of the same name.

merge() Function

As mentioned above, the default outer scope for metric expressions contains a merge() function. This function takes two or more row streams as arguments, and merges them into a single row stream. This is generally used for state machines, which are described more below.

This example merges events for status changes and assignee changes:

merge(
  ticket.onChange('status'),
  ticket.onChange('assignee')
)

On its own, this example would count both status and assignee changes, but it also enables more powerful state machines described next.

The merge function takes two or more arguments, each having the same type:

Row Expression - This is a sub-expression that specifies a row source. It can take other merges and state machine outputs as inputs. The only restriction is that inputs to merge cannot yet have the final duration type or join-all methods called (described in the Final RowStream Methods section), which must only be added at the end after any state machine or merge.

RowStream stateKeys() Method

The next method you can call on row streams is stateKeys(). This method must be followed by a call to the stateMachine() method or a state machine shortcut method like firstEvent(). The stateKeys() method must come first so it is described here first, but you might want to read the state machine section and come back here.

The stateKeys() method overrides the default value used to uniquely identify state machines, which is the ID of the base object, or, if multiple row streams are merged together with different base objects, the ID of the base object in the first merge argument.

For example, if you wanted to compute cycle times at the epic level for ticket events, you might do something like the following (level1parent is the property name for Jira tickets at the epic level):

ticket
  .onChange('status')
  .stateKeys(ticket.get('level1parent'))

You can also specify a literal to just run one state machine across all objects, which lets you do things like run a state machine for any open high-severity tickets in the organization:

ticket.onChange('status').stateKeys(1)

The stateKeys() method can have two argument counts. It can have a single argument, or one argument for each row stream in the preceding merge function, which is helpful when using a state machine on objects of different types. If there is only one argument for multiple merged row streams, then that argument will be applied the same way to all merge inputs, so it is effectively like calling stateKeys() with multiple arguments that are the same.

The arguments to the stateKeys() function take the following format:

State Key Expression - A sub-expression with the full set of functions available that evaluates to any non-object literal value (i.e., number, boolean, date, string, but not array or object). Here, the full set of primary object tables are technically in scope and can be referenced. However, unlike in property expressions, any object references that have not been added with an explicit join() call in the input row stream will evaluate to null. Any rows where the state key expression evaluates to null will be dropped and not run through the state machine.

RowStream stateMachine() Method

The next method you can use on row streams is stateMachine(). This method will run all of the input rows through a state machine, using a distinct state machine for each value produced by stateKeys() or for each base object ID if stateKeys() is omitted.

State machines allow you to implement more complex logic on sequences of property values and durations, including from different sources.

The state machine function takes one or more arguments that define each state in the state machine. The following example counts tickets completed, but only emits an event the first time a ticket is completed and does not output an event if a ticket is completed additional times after being reopened:

ticket
  .onChange('statusCategory')
  .filter(value.in('Done'))
  .stateMachine(
    state.on(0, 1),
    state.outputEvent()
  )

The state machine shown above is such a commonly used idiom that it is available in minQL as a firstEvent() method, so the following expression is exactly equivalent to to the one above:

ticket
  .onChange('statusCategory')
  .filter(value.in('Done'))
  .firstEvent()

The stateMachine() method can have one or more arguments, the format of each being:

State Definition - A StateDefinition object for the state at the argument index (0-based). The only thing in scope for this argument is state, which is a base StateDefinition object that must have methods called on it to define behavior of the state machine when it is in this state.

We will describe the state object methods in the upcoming StateDefinition Methods section.

RowStream firstEvent() Method

The example in the previous section of a state machine with two states that only outputs the first event for each object ID is such a common pattern that it is available in minQL as the firstEvent() method.

This method is exactly equivalent to calling the state method with the following arguments, as shown in the previous section:

.stateMachine(
  state.on(0, 1),
  state.outputEvent()
)

The firstEvent() method takes no arguments.

StateDefinition Methods

This section describes all of the methods available on StateDefinition objects, which define state machine behavior.

Unlike the RowStream methods in the previous section, the StateDefinition methods may be chained together in any order, and the order does not affect the behavior of the state.

StateDefinition on() and onEnd() Methods

The most basic method in a state definition is on(), which transitions to a different state when receiving an input row.

The on() method takes two arguments:

Input Index - A literal integer (no sub-expressions allowed) specifying the index of the input that will cause a state transition. The index comes from the argument index of merge(), or is 0 if operating on a single row stream.
State Transition Index - A literal integer (no sub-expressions allowed) specifying the 0-based index of the state to transition to when receiving the input.

For duration inputs, the transition will happen at the start of the duration. For other inputs, it will happen at the event time.

To transition to another state at the end of a duration input, you can use the onEnd() method, which takes the same arguments as on().

When dealing with durations, you can use both on() and onEnd(), which will use the same duration input row twice at different times.

This example generates an output when an event happens in the middle of a duration. It works by putting the state machine in a state at the start of a duration that will move to an output state when receiving a second event (index 1), but moving back to a state that will ignore the second event when the duration ends:

stateMachine(
  state.on(0, 1),
  state.onEnd(0, 0).on(1, 2),
  state.outputEvent()
)

StateDefinition Increment and Decrement Methods

The state in a state machine is a number indicating the current position. Normally, you must define a state as a state machine argument for each position that you use.

However, minQL state machines also support having a dynamic state as the last argument so that the state position can exceed the number of state definitions. This lets you count an arbitrarily large number of events, and emit outputs based on transitions from or to the count being a lower fixed number.

To support arbitrarily large state indexes, you can use the incrementOn(), incrementOnEnd(), decrementOn(), and decrementOnEnd() methods on the last state definition. The increment methods will increase the state index by one, and the decrement methods will decrease it by 1. The End methods will operate on the end of a duration.

Each of these methods takes one argument:

Input Index - A literal integer (no sub-expressions allowed) specifying the index of the input that will cause a state transition. The index comes from the argument index of merge(), or is 0 if operating on a single row stream.

The following example outputs durations where there is one or more open “P0” priority ticket:

ticket
  .duration('statusCategory')
  .filter(and(
    value.in('To Do','In Progress'),
    ticket.getOrig('priority').in('P0')
  ))
  .stateKeys(1)
  .stateMachine(
    state.on(0, 1)
      .outputDurationSinceFirstAfterOutput(1),
    state.incrementOn(0).decrementOnEnd(0)
  )

In this example, the state index will keep increasing as open P0 tickets increase. When the open P0 ticket count goes to zero (either by the ticket being completed or de-prioritized), the decrementOnEnd() method will trigger a transition back to the first state, which will output a duration since the state machine transitioned to the second state (a ticket entered a duration where it was open and P0).

StateDefinition goTo() Method

There may also be cases where you want to immediately transition to another state after generating an output. The goTo() method will immediately transition to another state when reaching the current state. Transitions that happen with goTo() will not trigger any other goTo() transitions on the destination state and will not generate output (i.e., the transition will happen “silently”).

In the example above, if you add goTo() to the last state, then you can emit multiple outputs if there are multiple events that happen inside of a duration (without generating an output when the duration starts), rather than just outputting the first one:

stateMachine(
  state.on(0, 1),
  state.onEnd(0, 0).on(1, 2),
  state.outputEvent().goTo(1)
)

The goTo() method takes one argument:

State Transition Index - A literal integer (no sub-expressions allowed) specifying the 0-based index of the state to transition to when reaching this state.

StateDefinition outputEvent() Method

The outputEvent() method will output an event row when reaching the state. This event row will contain all the attributes of the event that caused the transition, including the results of any value() or join() methods, the base object ID, and the time of the event.

The outputEvent() method takes no arguments.

StateDefinition Time Output Methods

There may be cases where you want to output the amount of time that has elapsed between states, which can be useful for calculating cycle times.

When calling a time output method, the state machine will set the value of the row to be the elapsed time, overriding any previous value set by the value() method.

Note that the output of a time output method will still take place at the point in time of the event that triggers the output. This means cycle time calculations will output the full duration at the point the cycle time ends. So, a report with a recent date range will include cycle times that started before the report date range, as long as they ended within the date range.

State definitions offer three different time output methods: outputTimeSinceFirst(), outputTimeSinceFirstAfterOutput(), and outputTimeSinceLast(). Each of these functions takes one or more arguments of the following type:

Previous State Index - A literal integer (no sub-expressions allowed). Calculate the time since a transition was made to a state at this index.

The behavior of the three different functions is as follows:

outputTimeSinceFirst() - Compute the time since the very first transition to one of the argument states.
outputTimeSinceFirstAfterOutput() - Compute the time since the first transition to one of the argument states following the last state machine output.
outputTimeSinceLast() - Compute the time since the most recent transition to one of the argument states.

While these functions will override any value set by previous value() method calls, they will pass through object IDs from the base object and any join() method calls from the event that triggered the output.

StateDefinition Duration Output Methods

While the time output methods are helpful for reporting cycle times, you may also want to compute the aggregate amount of time spent in certain states during the reporting period and broken down by other property values.

To handle this use case, state machines can output durations using one of the duration output methods: outputDurationSinceFirst(), outputDurationSinceFirstAfterOutput(), and outputDurationSinceLast().

Each of these functions takes the same arguments and has the same behavior as the corresponding time output method described in the previous section. The only difference is that they do not set a value and instead output duration rows covering the time interval.

StateDefinition saveValue() Method

There are scenarios where you may want to output a value in a state machine that is associated with an earlier event rather than the event that triggers an output.

This lets you do things like output the story points at ticket creation when some later event occurs, such as merging a pull request.

To save the value associated with the row that caused a transition to a state, you can call the saveValue() method on that state. This value will then override the value in subsequent event outputs, even if the current value is null. If multiple transitions happen to a state with saveValue() set for the same variable name, the most recent value will be used.

The saveValue() method takes one optional argument:

Variable Name - (Optional, defaults to 'result') A literal string specifying the name of the value to save. If you called the value() method earlier with an alternate name, this lets you save other values from earlier events so they will be included in the state machine output, and can further be referenced in method calls after the state machine.

To save multiple values, you can call saveValue() multiple times on one state.

StateDefinition saveObjects() Method

You may also want to save the base object ID or object IDs added by join() method calls from earlier events when emitting an output.

The saveObjects() method will save one or more object IDs from events that cause a transition to the current state and include those object IDs in the output, overriding any object IDs in the event that caused the output. Subsequent transitions to the same state will override previously saved object IDs.

This example shows how to report the cycle time from ticket creation to close by original assignee at the time of creation:

merge(
  ticket.event('created')
    .join(person, ticket.get('assignee')),
  ticket.onChange('statusCategory')
    .filter(value.in('Done'))
).stateMachine(
  state.on(0, 1),
  state.saveObjects(person).on(1, 2),
  state.outputEvent()
)

Note here that you don’t have to save the ticket because the ticket ID will be the same in the output event. So, including ticket as an argument to saveObjects() would have no effect.

The saveObjects() function takes one or more arguments of the following type:

Primary Object Type - Save the ID of this object type from the row that transitions to this state. Must be one of the primary object tables. Nothing else is in scope and no sub-expressions are allowed.

StateDefinition onlyLast() Method

There are times where you may only want to generate an output the last time an event happens, but there is no subsequent event to trigger a state transition and output.

The onlyLast() method will cause the current state to only emit its output if the event that transitioned to the state is the very last input event of any type to the state machine. If other events occur afterward – even if they don’t transition to an output state – then no output will occur for the onlyLast() state.

The following example shows how to compute the cycle time from the first pull request for a ticket being opened to the last one being merged. (This is a default metric available in the custom report as “Ticket PR Open to Last Merge”).

merge(
  branch.event('prOpened'),
  branch.event('merged')
).stateKeys(branch.getId('ticketId'))
.stateMachine(
  state.on(0,1),
  state.on(1,2),
  state.outputTimeSinceFirst(1)
    .onlyLast().on(1,2)
)

Without the onlyLast() call on the final state, this would output a cycle time on every pull request merge for pull requests associated with the same ticket. Or, if on(1, 2) was also removed from the final state, it would only output a cycle time from the first pull request being opened to the first pull request being merged for a ticket.

The onlyLast() method does not take any arguments.

StateDefinition onDone() Method

When dealing with durations, the onlyLast() method won’t suffice because it would cause the final output duration to end at the time of the last event.

To enable outputting a duration that ends at the report end time for the final state, you can call the onDone() method. This will make a transition to another state at the end of time if the state machine ends in the current state. This transition will use any saved value or object IDs from the final event (or from saveValue() or saveObjects(), if those were called).

The onDone() method takes a single argument:

State Transition Index - A literal integer (no sub-expressions allowed) specifying the 0-based index of the state to transition to when reaching this state.

Because the onDone() transition happens right after the end of the reporting time period, it will have no effect for event or time outputs because it would happen after the reporting period and be filtered out. Furthermore, it won’t have an effect if there is already a duration that ends after the reporting period. In effect, onDone() allows you to close unterminated time intervals for state machines that output durations.

AggregateRowStream Methods

This section describes the methods you can call on RowStream objects that will convert them into AggregateRowStream objects, which represent streams of rows that have had aggregation applied (e.g., sum, average, etc.).

These methods can also be chained together. That is, they are methods of AggregateRowStream objects as well.

Once any of these methods have been called, you may no longer call the initial RowStream methods, pass the row stream into the merge function, or run it through a state machine.

The joinFrom() method must be called prior to any joinAll() calls, but otherwise the methods in this section can be called in any order without affecting behavior, though calling multiple methods of the same type will override any previous setting.

Schedule Weighting Methods

By default, row streams that emit durations will aggregate values as the number of elapsed calendar days. To change this behavior and weight time differently, you can use one of the methods in this section.

AggregateRowStream durationPersonWork() Method

minQL can compute time durations based on individual weighted schedules to produce a number of work days. You can do this by calling the durationPersonWork() method.

The durationPersonWork() method will compute work days using a 5-day work week by applying an automatically generated schedule for the person associated with the current row.

This is the minQL for the default “Dev Work Days” metric:

person.duration('activeWorkstream')
  .filter(idValue.notIn(null))
  .joinAll(branch, idValue)
  .durationPersonWork()

Because person was the base of the duration event, a person ID is available to look up a weighted schedule.

If the person ID is null or there is no person, then a default weighted schedule will be used, which counts weekend days as 1/8 of a work day each, and each week day as 0.95 work days spread evenly across the entire day (so it is timezone-agnostic).

See the Time Model Documentation for more information about how this 5-day work week is allocated

The durationPersonWork() method takes no arguments.

AggregateRowStream durationLog() Method

There are also cases where you may want to aggregate durations from active work time logs. The durationLog() method lets you do this. It just multiplies the elapsed days calculation by 3 so that 8 hours is one day instead of 24 hours.

The durationLog() method takes no arguments.

Time Unit Methods

The methods in this section control the units used for time durations. If none of these methods is called, the default units will be days.

These methods may be called in conjunction with durationPersonWork() or durationLog() to convert those values.

AggregateRowStream months() Method

Another common use case is calculating the total number of months elapsed, which is important for things like monthly accounting of cost capitalization.

When you’re dealing with work day durations, it can be especially unclear how many work days are actually in a given month because different people will have different work day weights for weekend days.

minQL offers the months() function for time durations, which divides the number of calendar or work days by the total number of days in the current month. This means that if a duration spans a whole month, then the output value will be 1 if you add a call to months().

The months() method takes no arguments and is mutually exclusive with timeCount(), which is described next.

AggregateRowStream timeCount() Method

In some cases, you may want to display the count of active durations, like the number of open tickets. The other interval functions will output days by default, so the active duration count will show up as you’d expect when running a report with a narrow date range that shows data on a per-day basis.

However, if you increase the time range so that individual data points are weeks, months, etc., then the number of days would be higher than the number of active intervals.

The timeCount() method normalizes data points based on the currently active time range so that it will count units of time equal to the current report date granularity, which may be weeks, months, quarters, or year. This way, the report will show the average count of active durations regardless of your date range.

The timeCount() method takes no arguments and is mutually exclusive with months().

The timeCount() method may be called in conjunction with durationPersonWork() or durationLog() to convert those values from days to active duration counts.

Object Joining Methods

AggregateRowStream joinFrom() Method

By default, row streams will be joined to other objects starting from the first primary object table that is referenced. For example, if you have a state machine that has a ticket and a branch input, the ticket will be used to look up other objects referenced in breakdown property expressions.

You can change this by calling the joinFrom() method. In the previous example, you may want the starting object to be the branch so that breakdown property expressions that reference a branch will use the one that caused the last state machine output rather than the just the first branch associated with the ticket input.

The joinFrom() method takes one argument:

Primary Object Type - Use this object type as the base of further joins to objects referenced in property methods. Must be one of the primary object tables. Nothing else is in scope and no sub-expressions are allowed.

AggregateRowStream joinAll() Method

The join() method described in the Initial RowStream Methods section operates on a single event stream. If you want to override the way that a row stream joins to other objects after you have run events through a state machine, then you can use the joinAll() method.

The joinAll() method works exactly the same way as join(), except that it is an AggregateRowStream method. See the documentation for join() for details about the arguments and behavior of joinAll().

The one difference with joinAll() as compared to join() is that all object types will be in scope for the join expression input of joinAll(), but will be null if the current row does not have an object ID of that type.

The example in the “RowStream durationPersonWork() Method” section shows how to set the branch to the currently active branch when referencing a duration from the person object.

Aggregation Function Methods

The default aggregation function for AggregateRowStream objects is sum. If you would like to use a different aggregation function, then you can call one of these methods.

Aggregation functions will be applied for each unique combination of breakdown property expression values.

Note that when you are using count-based values like a commit count, the value of each row prior to aggregation will be 1 (unless you have overridden the value), so aggregation functions other than sum don't make sense in this scenario.

Non-sum aggregation is more useful when you have set the value of a count-based metric to another property like story points, or if you are using a state machine that outputs a time interval (like a cycle time).

If the underlying value is a duration (like work days), then the aggregation function you specify will be ignored and instead sum will be used to calculate the aggregate duration length for each combination of property expression values. However, the aggregation function will be applied to those duration sums when computing total rows.

This comes into play when you have a chart with multiple breakdowns. For example, if you have specified average() for dev days and have breakdowns by team and person, the chart will show the average of dev days for each person when displaying results at the team level rather than the total sum (even though the results at the lowest breakdown level – person in this case – will be computed with sum).

minQL supports the following aggregation methods on AggregateRowStream objects. Each of these will ignore and exclude any null values rather than treating them as zero:

sum() - Use the sum of values. Can be omitted since it is the default.
cumulativeSum() - Use the sum of values, but accumulate it over time when displaying in a chart (table display values will still show the regular sum).
average() - Use the average of values.
min() - Use the minimum value.
max() - Use the maximum value.
median() - Use the median value, picking a specific value if the number of values is even rather than taking the average of the two middle values.
stddev() - Use the standard deviation of values.
percentile(<arg>) - Use the percentile of values. The required argument must be between 0 and 1. This function will pick a value rather than performing interpolation (i.e., it corresponds to the PERCENTILE_DISC rather than the PERCENTILE_CONT database function).
count() - Use the count of non-null values, including duplicates.
countUnique() - Use the count of distinct non-null values.

TableRowStream Methods

The earlier RowStream and AggregateRowStream objects produce aggregate values for each combination of breakdown property values in the custom report table rows.

To perform further operations on the aggregate values, minQL has a final TableRowStream object type.

The methods on this section can be called on a AggregateRowStream object to produce a TableRowStream object. These methods can also be chained together. That is, they are methods of TableRowStream objects as well.

These methods are not available on RowStream objects, so you must append .sum() to a RowStream object before calling any of these methods to first convert it to an AggregateRowStream.

Once any of these methods have been called, you may no longer call methods from RowStream or AggregateRowStream.

These methods can be chained together in any order and each operation will be applied in order.

Because these methods operate on the final aggregated rows in the table, they will also be applied separately to any summary "All" total rows independently of non-total rows, and may filter total rows from being displayed.

TableRowStream value() Method

The value() method allows you to apply an expression to aggregate results and alter the output value.

For example, if you want to convert dev work days into hours, you can do the following:

person.duration('activeWorkstream')
  .filter(idValue.notIn(null))
  .joinAll(branch, idValue)
  .durationPersonWork()
  .value(result * 8)

The first four lines are the base dev days metric. The final value() call will update the result to its value multiplied by 8.

The arguments for value() are as follows:

Value Expression - A sub-expression with properties and functions in scope from the TableRowStream expression scope (described in a later section) as well as any values previously saved by calling value() with an alternate variable name.
Variable Name - (Optional, defaults to 'result') This parameter must be a string literal starting with a lower-case letter and be alpha-numeric. If you specify this parameter, then instead of updating the table output result, the value produced by this function will be set as another variable that will be available in the scope of later methods (like other calls to value() or filter()) at the name you specify.

TableRowStream filter() Method

The filter() method allows you to filter out rows post-aggregation.

This example shows how to only display rows where the total number of dev days is greater than 5:

person.duration('activeWorkstream')
  .filter(idValue.notIn(null))
  .joinAll(branch, idValue)
  .durationPersonWork()
  .filter(result > 5)

Note that filtered rows may still show up if you have multiple measures/Y-axis values in your report and the rows have values for other metrics. However, in this case, the value from the filtered column will show up as empty/null. For example, if you used the formula above in conjunction with logged days, then rows that have logged days with less than 5 dev days will show an empty value for the dev days column.

The filter() method takes one argument:

Filter Expression - A sub-expression with properties and functions in scope from the TableRowStream expression scope (described in a later section) as well as any values previously saved by calling value() with an alternate variable name.

TableRowStream extend() Method

The extend() method adds the aggregate result from another TableRowStream to the scope of the current TableRowStream so that you can perform expressions on both values.

This example shows how to calculate the number of tickets created minus the number completed.

ticket.event('created').sum().extend(
  ticket
    .onChange('statusCategory')
    .filter(value.in('Done'))
    .firstEvent(),
  'completedCount'
)
.value(result - completedCount)

Here you can see how extend adds the result from the first argument RowStream at a name other than result so that it can be subtracted from the first TableRowStream value.

The arguments for extend() are as follows:

Extend Stream - An object of type RowStream, AggregateRowStream, or TableRowStream. If a RowStream is provided, then the default sum() method will be used for aggregation to produce results. The scope of this argument is the default scope for metric expressions (i.e., you can put any valid metric expression in this argument).
Variable Name - This required parameter parameter must be a string literal starting with a lower-case letter and be alpha-numeric. The result value from the first argument will be added to the scope of the base TableRowStream with this name so that you can reference it in subsequent method calls.

TableRowStream Expression Scope

The value() and filter() methods of TableRowStream take an expression as the first argument. This section describes the variables and functions available in the scope of those expression arguments.

First, the variable result contains the result of the aggregate function (or a previous override after you have called value()). Any named values from previous calls to value() will also be available.

All of the functions available in the default scope for property expressions are also available. For a full list of available list functions, see the minQL Function Reference.

The TableRowStream scope further includes a special breakdown() function and a selection of window functions, which are described in the sections that follow.

The breakdown() Expression Function

The TableRowStream expression scope includes a special function breakdown(), which lets you reference property expressions that are included in the custom report as breakdown values.

To use it, simply copy and paste a minQL expression from an existing breakdown value as the argument to breakdown.

For example, to multiply logged hours by a daily rate of 500 (e.g., to represent dollars) by default and 1000 for a particular person, you can do:

person.duration('activeWorkstream')
  .filter(idValue.notIn(null))
  .joinAll(branch, idValue)
  .durationPersonWork()
  .value(result * (
    breakdown(person.get('id'))
      === 'Bob' ? 1000 : 500
  ))

If you pass in an expression that does not exist as a breakdown in the report, then the formula will generate an error.

The breakdown() function returns the value of the breakdown expression for the current row.

If the current row is an "All" row showing the total rolled-up result for all rows having the same earlier breakdown value, then the breakdown() function will return the string '<total>'.

The breakdown() method takes one argument

Breakdown Expression - A minQL expression that exactly matches the minQL for a current breakdown in the custom report.

TableRowStream Expression Window Functions

There are some cases where you may want to apply functions to multiple rows after aggregation has already occurred. To support this use case, window functions are available in the scope of value() and filter() expression arguments.

This example shows how to divide the result by the sum of all values:

.value(result / sum(result))

You can also use window functions to compute things like moving averages.

Note that when dealing with "All" rows that show the total rolled-up result, minQL will give each rollup level its own partition behind the scenes. In the above example, this means that the return value of sum would be the sum of all rows prior to the rollup if there is one breakdown, rather than double-counting the sum of the rollup value and all the original values.

If you computed the moving average with total rows, this means that the moving average result for the total rows would be the moving average of the totals.

minQL supports the following table functions:

count(<arg1>, ..., <argN>) - Corresponds to the window function syntax of COUNT
sum(<arg1>) - Corresponds to the window function syntax of SUM
average(<arg1>) - Corresponds to the window function syntax of AVG
min(<arg1>) - Corresponds to the window function syntax of MIN
max(<arg1>) - Corresponds to the window function syntax of MAX
median(<arg1>) - Corresponds to the window function syntax of MEDIAN
percentile(<percentile_value>) - The one argument must be a literal number (no expressions allowed) between 0 and 1. Corresponds to the window function syntax of PERCENTILE_DISC

Each of the window functions (other than percentile) takes an argument with the TableRowStream expression scope (except it cannot contain nested window functions). percentile() takes one number argument that cannot be an expression.

Window partitions, ordering, and frames are controlled with additional methods on the window function, which are described next. These methods can be chained together to control the behavior of window functions.

Window Function distinct() Method

For functions that support a distinct argument (see the linked Snowflake documentation), you can get the DISTINCT version of the function by appending .distinct() to the the call, like this:

.value(average(result).distinct())

Window Function partitionBy() Method

By default, window functions will run against all rows at the same level of rollup aggregation (or just all rows if the report does not have a chart or summarize by method set).

To control the window function partitions, you can call the partitionBy() method. For example, to separately compute the average of values less than and greater than 100, you could do:

.value(average(result).partitionBy(result > 100 ? 1 : 0))

The partitionBy() method takes one argument:

Partition By Expression - The value to partition by. This argument has the TableRowStream expression scope, but cannot have nested window functions.

Window Function orderBy() Method

To control ordering within the partitions, you can call the orderBy() method on the window function result. The percentile() function requires an order by, and an order by is required with window frames. Otherwise, it is optional.

The following example shows how to compute the 90th percentile of the result:

.value(percentile(0.9).orderBy(result))

You can chain multiple orderBy() calls together to do multiple orderings, though percentile() and range window frames only allow a single orderBy() call.

The orderBy() method takes one to three arguments:

Order By Expression - The value to order by. This argument has the TableRowStream expression scope, but cannot have nested window functions.
Is Descending - Default false. If set to true, then do a descending sort.
Reverse Null Order - Default false. If set to true, then nulls are put at the start for an ascending sort, or at the end for a descending sort.

Window Function rowsBetween() Method

To apply a row frame to a window function, you can call the rowsBetween() method on the window function result.

For example, to compute a running sum up to and including the current result ordered by the result:

.value(
  sum(result)
    .orderBy(result)
    .rowsBetween(null, 0)
)

The rowsBetween() method takes two arguments:

Range Start - An integer or null value. If null, then the frame will use UNBOUNDED PRECEDING. Otherwise, 0 means the current row, negative numbers mean a number of preceding rows, and positive numbers mean a number of following rows.
Range End - An integer or null value. If null, then the frame will use UNBOUNDED FOLLOWING. Otherwise, the numbers have the same meaning as range start. Note that this means rowsBetween(-1, 1) is what you would use for the equivalent SQL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING.

Window Function rangeBetween() Method

To apply a range frame to a window function, you can call the rangeBetween() method on the window function result.

For example, to compute a sum of values within 1.5 of the current value:

.value(
  sum(result)
    .orderBy(result)
    .rangeBetween(-1.5, 1.5)
)

The rangeBetween() method only allows a single order by value, which must evaluate to a number.

The rangeBetween() method takes two arguments:

Range Start - A numeric or null value. If null, then the frame will use UNBOUNDED PRECEDING. Otherwise, 0 means the current row, negative numbers mean a preceding quantity, and positive numbers mean a following quantity.
Range End - A numeric or null value. If null, then the frame will use UNBOUNDED FOLLOWING. Otherwise, the numbers have the same meaning as range start.

Window Function intervalRangeBetween() Method

To apply range frames with date values, you can use the intervalRangeBetween() method.

For example, to compute an average over all rows falling in the past 3 and next 2 days:

.value(
  sum(result)
    .orderBy(
      breakdown(time.getOrig('day'))
    )
    .rangeBetween(-3, day, 2, day)
)

This is equivalent to the SQL SUM(result) OVER (ORDER BY day RANGE BETWEEN interval '3 day' PRECEDING AND interval '2 day' FOLLOWING).

The rangeBetween() method only allows a single order by value, which must evaluate to a date or time.

The rangeBetween() method takes four arguments:

Range Start - A numeric or null value. If null, then the frame will use UNBOUNDED PRECEDING and the starting date part argument will be ignored. Otherwise, 0 means the current row, negative numbers mean a preceding interval, and positive numbers mean a following interval.
Starting Date Part - One of: millisecond, second, minute, hour, day, week, month, quarter, year. This date part is appended to the range start argument to form an interval.
Range End - A numeric or null value. If null, then the frame will use UNBOUNDED FOLLOWING. Otherwise, the numbers have the same meaning as range start.
Ending Date Part - One of: millisecond, second, minute, hour, day, week, month, quarter, year. This date part is appended to the range end argument to form an interval.