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.
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(), getIdOrig().

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.

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.

In the sections that follow, the methods must generally be called in the order listed here. Some support multiple chained calls (like join), while others can only be called once (like value).

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 row streams before calling final methods that control the output.

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 'value') 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 value, 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 value. 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. 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 deprioritized), 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 'value') 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.

Final RowStream Methods

This section describes the final methods you can use on RowStream objects. Once 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.

These methods control the final output and join behavior of the overall metric expression.

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.

RowStream durationPersonWork() Method

By default, row streams that emit durations will aggregate values as the number of elapsed calendar days.

However, minQL can also 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.

RowStream 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.

RowStream 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.

RowStream 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().

RowStream 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 it can run after state machines. 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, 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.