Document formulas

What is a document formula?

A document formula is a value calculated at the document scale from variables coming from other document formulas or from post-it extended fields. Used variables can be of numeric, duration or date type.

The result of a document formula can be of numeric, duration or date type depending on the specified expression.

This value will be available in your document and can be used to create filters, calculation fields or others document formulas. 

The document formula tab

Start by clicking on the "Document formulas" icon (1) in the toolbar.

If you just started using formulas, this tab is probably empty.

For each line you can find : the formula's name  and its calculated value.

Click a formula (2) to display more details, including the applied filter and its expression.

In this example, the "Takt Time" formula (2) has a filter that limits its scope (only Post-it notes matching the specified criteria are included). Its expression is made up of the "Opening Time" and "Customer Demand" variables defined above, and the result of the calculation is 420.

Create a document formula

In order to create a formula, click on "New formula" (3).

The configuration window is displayed.

In the "Name" section, enter the name of your formula.

Click "Filter Data" to add a filter.

The "Filter Data" section is currently empty.

Adding a filter allows you to limit the scope of your formula. Only Post-it notes that match the selected criteria will be included in the calculation.

Click "Add Criteria" to display the list of available criteria.

List of available criteria

• Text :

Search for one or more words in the main text of the items.

• Type :

Filter items by type: Post-its or JIRA cards.

• Color :

Filter items based on their color.

• Sticker: 

Filter items based on the stickers assigned to them.

Click on "Sticker" in the dropdown menu; the configuration window opens.

1 . Select an operator: less than, exactly, at least.

2 . Build the target sticker set by adding or removing stickers.

3 . Click on a sticker to change its color.

• Crossed / Not Crossed

FIlters items that are crossed or not.

• Shape :

FIlters post-its items based on their shape.

• Extended field :

Filter post-its based on the content of an enriched field.

Click on "Extended fields" in the dropdown menu, the list of the fields of the document opens.

Select the target field from the list.

- For a text field:

Filter Post-its whose "target" field contains the entered text.

1 . Enter the text to search for.

2 . Select “None” to filter post-its with an empty field.

- For a numerical field:

Filter post-its whose field value meets the chosen condition (operator & target value).

1 . Choose an operator: less than, equal to, greater than.

2 . Enter a target value. This value can be numeric or can reference document variables (extended fields, document formulas). Press the @ key to access the variables. The target value must be numeric so it can be compared to the field’s result.

For more information about variables : Document formulas, Calculation field, Detail of operations on variables.

Select “None” from the list of variables to filter post-its with an empty field.

- For a duration field:

Filter Post-its whose selected field value meets the chosen condition (operator & target value).

1 . Choose an operator: less than, equal to, greater than.

2 . Enter a target value. The expression may use numeric values and/or reference document variables (enriched fields, document formulas). Press the @ key to access the variables. The target value must be a duration type so it can be compared to the field’s result.

For more information about variables : Document formulas, Calculation field, Detail of operations on variables.

Select “None” from the list of variables to filter Post-its with an empty field.

- For a date field :

Filter Post-its whose selected field value meets the conditions (see below).

1 . Choose “No date” to filter Post-its whose field is not filled in.

2 . Lower bound: the field value must be after the target date. The expression can use numeric values and reference document variables (extended fields, document formulas). Press the @ key to access the variables. The target value must be of  date type so it can be compared to the field’s result.

If the field is left empty, the condition will be ignored.

For more information about variables : Document formulas, Calculation field, Detail of operations on variables.

3 . Upper bound : the field value must be before the target date (works the same way as the lower bound).

- For a member field :

Filter post-its whose selected field value matches the target value.

1 . Choose the target member from the list.

2 . Select “None” to filter post-its with a member not filled in.

- For a list field:

Filter post-its whose selected field value matches the target value.

1 . Choose a target result from the list.

2 . Select “None” to filter post-its with a list not filled in.

- For a calculation field : 

Filter post-its whose selected field value matches the target value.

Creating criteria for calculated fields will differ depending on the type of the calculated field (numeric, duration, date). See the cases above: duration type, date type, numerical type.

•  JIRA fields :

Filter post-its whose selected field value matches the target value.

The JIRA fields available on Ubikey JIRA cards are: Assignee, Due Date, Epic Name, Issue Type, Labels, Priority, Status.

The fields Assignee, Epic Name, Issue Type, Labels, Priority, Status are treated as text fields (see above). You will need to enter the exact target result in the field (exact spelling required).

The Due Date field is treated as a date field (see above).

For more information on Ubikey / JIRA integration: JIRA Integration.

• Group :

Filter items that are present in the selected group(s).

1 . Select the target group(s) from the list of groups in the document. If multiple groups are selected, the created criteria will be: belongs to group 1 or group 2, etc.

2 . Select “None” to filter items that are not in any group.

Composition du filtre

1 . Add a criteria.

2 . Choose an operator: and / or.

3 . Click on a criteria to: Edit the criteria, Exclude the criteria, or Delete the criteria.

Compose Your Formula

To create your formula, you can use numeric values, basic mathematical operators (+, -, /, *, (, )), and variables from your document.

To call a variable, use the @ key of your keyboard and choose the variable in the list.

(6) Search for a variable

(7) Filtrer the variables list by type.

(8) List of available variables, click on a variable to add it to the expression. 

(Détail of available variable types.)

For variables from the post-its fields (each post-it can have a value for the field), 4 functions are availables :

• Sum (of all values entered on the post-its)
• Average (of all values entered on the post-its)
• Min (smallest value among entered values on the post-its, earliest value for dates)
• Max ( biggest value among entered values on the post-its, latest value for the dates)

Attribution of a type (unit) to the formula

(8) While entering an expression, a type (numeric, duration or date) will be automatically attributed to the formula depending on the used variables (see : Detail of variable operation)

Errors management

Some operations are not autorised because they are mathematically invalid or because we do not support the resulting unit (see: Detail of operation on variables)

(9) The formula field displays an error and the error details below. You must edit the expression or convert a variable (see below) to make the formula valid again. 

Conversion of variables

In some cases it might be usefull to convert a duration variable into a numeric variable to solve an invalid operation. For that, right click on the variable you want to convert and choose the conversion unit.

For example : I want to convert my duration variable (value = 2 minutes) into a numeric variable in seconds (value = 120).

You can also convert a numeric variable into a duration variable with the same process.

Edit an existing formula.

To edit a document formula, click on the "menu" icon next to the formula and select "Edit".

You can change the name and the expression of the formula (see above).

(10) In the case of a change that would lead to a type change for the formula, the "Dependencies" section enables you to check if the formula is used elsewhere (document formula or extended field) and if the type change makes dependencies invalid or not.

Delete formula

To delete a document formula, click on the "menu" icon next to the formula and select "Delete".

Display formula's result within your document.

You can drag and drop a formula from the "Document formulas" tab to add the formula's result to your document or click on the icon menu and select "Display in document".