Page Comparison

Produces an array of the defined values for the item and/or its sub-items. 

Image Removed

Image Added

Accepts modifiers: #ancestors, #children, #leaves, #strict, #subtree, #fromLevel, #toLevel, #reverse, #distinct (does not include duplicate values), #flatten (if function produces an array, includes the inner values, rather than the array), #compact 

Avg calculates an average of the defined values for the item and/or its sub-items. The result for avg is generally the same as sum/count. It returns nothing if there are no defined values for {x}.

Accepts modifiers: #all, #ancestors, #children, #leaves, #strict, #subtree, #preceding, #levels (together with #preceding), #baseLevel (together with #preceding).

Count calculates a count of defined values (or truthy values, if the #truthy modifier is specified) for the item and/or its sub-items.

Image Removed

Image Added

Accepts modifiers: #all, #ancestors, #children, #leaves, #strict, #subtree, #truthy, #preceding, #levels (together with #preceding), #baseLevel (together with #preceding).

Join concatenates (joins) strings from the item and its parents (or other items, if modifiers are used).

• By default, it joins all parent string values from root to the self value.

• If the current row has children and the #subtree modifier is set, join appends the values for children, wrapping them into characters (braces by default).

• Wrapping characters can be set by #beforeChildren and #afterChildren (see example for #subtree to see how it works).

Image Removed

Image Added

Accepts modifiers: #ancestors, #children, #leaves, #strict, #subtree,  #reverse, #separator, #beforeChildren, #afterChildren, #fromLevel, #toLevel, #distinct.

Max returns the maximum defined value for the item and/or its sub-items. Numeric, date, duration and text fields can be compared. Text fields are compared lexicographically.

Image Removed

Image Added

If the formula produces an array, this will find the maximum element within that array.

Accepts modifiers: #children, #leaves, #strict, #subtree.

Produces the median value. Works the same as PERCENTILE#0.5.

Undefined are ignored. Non-number values result in an error.

Accepts modifiers: #ancestors, #children, #leaves, #strict, #subtree, #fromLevel, #toLevel

Min returns the minimum defined value for the item and/or its sub-items. Numeric, date, duration and text fields can be compared. Text fields are compared lexicographically.

Image Removed

Image Added

If the formula produces an array, this will find the minimum element within that array.

Accepts modifiers: #children, #leaves, #strict, #subtree.

Parent extracts the value from the parent row or from an ancestor row by a specified level.

Image Removed

Image Added

Using the #level modifier, you can specify which row to extract the value from:

• PARENT#level=-1{} extracts the value from the parent row (same as PARENT{}) 

• PARENT#level=-2{} extracts the value from the grandparent row

• PARENT#level=1{} extracts the value from the root row

Accepts modifier: #level. 

Calculates a percentile value from the defined values for the item and/or its sub-items. The hierarchy of values is ignored – all subject values are treated equally.

Must include the modifier #p, followed by the desired percentile (p=0.95, p=95). p=1 interpreted as 1%.

Undefined are ignored. Non-number values result in an error.

Accepts modifiers: #ancestors, #children, #leaves, #strict, #subtree, #fromLevel, #toLevel, #p (required)

Sum calculates a numerical total for the values calculated for the item and/or its sub-items. 

Image Removed

Image Added

Other variations of SUM allow different types of aggregation:

• SUM{} (the same as SUM#subtree{}) aggregates values from all descendants 

• SUM#children{} aggregates values from direct children only

• SUM#leaves{} aggregates values from leaves

• SUM#preceding{} aggregates values from the preceding rows

• If the formula produces an array, calculates the total of the elements of that array

Note that when the value of the expression under aggregation is not numeric (and cannot be converted to a number), it is ignored.

Accepts modifiers: #all, #children, #leaves, /wiki/spaces/structure/pages/3667890952, /wiki/spaces/structure/pages/3667890952, #preceding, #strict, #subtree, #levels (together with #preceding), #baseLevel (together with #preceding).

Process the whole subtree of the current row. This is the default behavior for sum,   count, avg, min, max .

JOIN#subtree{X}

Can be used with: array, sum, count, avg, join, min, max, median, percentile, quartile1, quartile3, values.

Specifies the position of the last row the aggregate function should take as input for a sequence.

Position is specified by an integer parameter denoted as n below:

• Positive values mean the absolute depth of row in the structure, e.g. n=1 means root.

• Negative values mean the depth relative to current row, e.g. n=-1 is the current item's direct parent.

• 0 means current row.

• Default is 0.

This modifier does not work with any tree types except #ancestors.

JOIN#toLevel=-1{X}
JOIN#toLevel=2 {X}

Can be used with: array, join, median, percentile, quartile1, quartile3, values.