Cell formulas description

TreeGrid v5.9

TreeGrid documentation index

 

There can be columns marked as calculated with one expression for whole column. Expression is string in parameter Formula (if set, column is calculated). These columns cannot be edited.

There can be rows marked as calculated with different expressions in every cell. Calculated row must have set attribute Calculated. Every calculated cell in row has own Formula parameter. If cell has not set Formula, it is not calculated even if this column is calculated.

Calculated cells cannot be edited.

Remember, if you use TreeGrid.dll/so as server application, not all expressions are possible, see Server calculations.

 

Input values

In formula you can use cell values from the same row. These values are identified by column name. If you use calculations, choose column names and fixed rows ids carefully to not conflict with other keywords.

You can use cell values from all fixed rows. These values can be accessed by function Get (id, “column”). id is keyword (row’s id), but column is string with column name.

You can use cell values from immediate parent row. These values can be accessed by function Get (Parent, “column”). Parent is keyword (exact word Parent), but column is string with column name.

You can also access any other row’s attributes given in input data, even any user defined attributes by Get (Row, ”attribute”). Row is keyword (exact word Row), but attribute is string with attribute name. Instead of Row you can use also Parent or id as mentioned above.

You cannot use any other cell values from variable rows, except those were mentioned above.

You cannot use relative reference to other row or column.

If expression results to NaN (division by zero, computing nulls, ...) it returns empty string for string or 0 for number or false for boolean. If you use wrong keyword (row, column or function name), this will lead to result of NaN too.

If any input value is null and you use some arithmetic operation it will result to NaN too.

Thus, set explicitly all cell values used in formulas, you can do it also in defaults. If value is not set, it is usually displayed as 0 according to its formatting, but cannot be used in formula!

If you have any problems with calculations, you can set <Cfg DebugCalc=’1’> to see all errors in formulas.

 

Calculation order

Cells in row are calculated in order given by CalcOrder attribute – it is comma separated array string of column names. If CalcOrder is not set for the row, cells are calculated in alphabetical order.

If CalcOrder is set, only these cells are calculated, even if other cells have Formula attribute.

In vertical direction, first are calculated body rows and after them fixed rows. In tree, first are calculated children and after them parent row.

You can change the direction by advanced calculation order, see below.

Thus, if you use another calculated cell value as input in formula, you can reference only cells, which are prior (in calculation order) to the actual cell !

 

Advanced calculation order

You can control calculation order by more advanced way:

a) Fixed rows’ cells you can calculate by random by setting <Cfg> CalcOrder attribute, and there you can set individual cells by id$col, for example “F1$A,F2$C”

b) In any row’s CalcOrder or global CalcOrder you can set position of calculating row’s children by placing ‘*’ to CalcOrder

c) You can define more calculating phases in tree by dividing CalcOrder to more attributes: CalcOrder, CalcOrder1, CalcOrder2 ,...

                and you can reference these CalcOrders in parent CalcOrder by *X, where X is number in CalcOrder (1,2,...).

For example you can have parent’s (or global) CalcOrder = “*,A,B,*1” and all children’s CalcOrder = “*,A,B” and CalcOrder1 = “C,D,*1”

                => The grid will calculate columns A,B upstairs (children first) and after it will calculate columns C,D downstairs (parent first)

See also CalcOrder tutorial.

If you use advanced calc order, you usually need to set Recalc attribute of all rows to 256 – to recalculated whole grid after change.

 

Tree

In the tree, all aggregate functions in row iterate row’s immediate children only, without their children.

If the row is fixed, aggregate functions iterate all variable root rows without their children.

By default, row’s children are computed before the row or you can use advanced calculation order by asterisks, see above.

Deleted rows are not iterated in aggregate functions.

 

Operators

Operators use standard C++/JavaScript syntax: +, -, *, /, ! (not), % (modulo), & (bit AND), | (bit OR), ^ (bit XOR), && (logical and), || (logical OR), <<, >> (bit shift), == (equals), != (not equals), <=, >=, <, >.

Priority of operators is the same as in JavaScript. Always you can use ( ).

It can also be used operator (condition ? true_result : false_result). This operator has the least priority so you must use () for other calculation.

Remember, the ‘&’ and ‘<’ characters are XML entities and must be replaced by &amp; and &lt;

 

Keywords

Predefined keywords for TreeGrid are column names, row ids, aggregate function names, any custom function names, Get, Row, Col, Grid, Children and val. These keywords must not conflict with each other and with all JavaScript reserved keywords. Keywords are not quoted in formulas. Keywords are case sensitive.

JavaScript reserved keywords are: break, case, catch, continue, default, delete, do, else, false, finally, for, function, if, in, instanceof, new, null, return, switch, this, throw, true, try, typeof, var, void, while, with.

 

Data types

Data type can be number or string.

Number is used for TreeGrid types Bool, Int, Float, Enum, Date.

String is used for text columns. Html and Pass types cannot be used formulas.

Enum type is index to Enum array.

Date is number of milliseconds from GMT 1/1/1970 00:00:00. It corresponds to JavaScript Date.getTime() function.

Type conversions are the same as in JavaScript. Example: number + string = string, number – string = number.

 

Constants

Number constants are written normally in English (C++/JavaScript) notation. String constants are in quotes or double quotes. If string contains quote, double quote or backslash it must be preceded by backslash.

Date constants are in seconds from 1/1/1970 00:00:00 GMT, so you can add constant to datetime or test two dates if less/greater. For example to add one day you need to add constant 24*60*60*1000.

Constants also can be calculated.

 

White space

White space characters <=32 are ignored except in string constants.

 

Function calls

Function calls use standard C++/JavaScript syntax: funcname (argument1, argument2, ...).

Function names are case sensitive. All TreeGrid function names are written in lower case except Get macro.

 

 

Mathematical functions

 

You can use global object Math with all standard JavaScript functions and constants (for example Math.abs(col1))

Constants

E                            e = 2.718281828459045

LN10                     ln (10) = 2.302585092994046

LN2                       ln (2) = 0.6931471805599453

LOG10E              log 10 (e) = 0.4342944819032518

LOG2E                log 2 (e) = 1.4426950408889633

PI                           π = 3.141592653589793

SQRT1_2             1 / √2 = 0.7071067811865476

SQRT2                 √2 = 1.4142135623730951

 

Functions            

random ( )            random number in range 0.0 – 1.0

abs (x)                   absolute value

round (x)              rounds to integer, for example 2.5 => 3,  -1.4 => -1, -2.5 => -2

ceil (x)                   nearest bigger number, for example 1.3 => 2, -1.3 = > -1

floor (x)                nearest smaller number, for example 1.3 => 1, -1.3 = > -2

exp (x)                   e power x

log (x)                   ln x, decimal logarithm is LOG10E * log(x), binary is LOG2E*log(x).

pow (x, y)             x power y.

sqrt (x)                  √x

sin (x)                    sin x, in radians

cos (x)                   cos x, in radians

tan (x)                   tan x, in radians

acos (x)                 arccos x, x must be in range –1.0 to 1.0

asin (x)                  arcsin x, x must be in range –1.0 to 1.0

atan (x)                 arctan x

atan2 (y,x)           arctan x/y

 

Other functions

maximum (a,b,c,...)           returns maximum of given values

minimum (a,b,c,...)            returns minimum of given values

 

Aggregate functions

 

All aggregate functions iterate all children of the row, where is function used. If row has not children returns 0.

In fixed rows aggregate functions iterate all variable root rows (in pure grid all variable rows).

First parameter of any function is column name in quotes or “” for actual column, if function has only one parameter, the column name can be omitted to use actual column.

When used server paging (ChildPaging=3 or Paging=3), the not yet loaded body or parent row must contain result of the function. The attribute name is “col + function_name”, for example Col1sum for column named Col1. For sumif and countif the name is “col + function_name + ecol”. For countrows is “countrows + type”.

 

sum (string col)                   Sums all values in column

sumsq (string col)               Sums all squares of values in column

sumif (string col, string condition, string ecol)           Sums all values in column ecol, where the value in column col satisfies condition.

                If ecol is missing, sums values in col, if function has only one parameter (condition), sums actual column

                condition is code to evaluate, must return boolean value.

condition is running in context of TCalc and actual value of column col is in property val. In very simple condition can be keyword val missing.

                Examples: sumif(“A”,”<5”,”B”)     Sums values in column B in rows that have in column A value less then 5.

                               sumif (“A”, “val>=5 && val<10”)  Sums values in column A in rows that have in column A value in range <5,10).

                               sumif(“val<5 && B<10 || val>=5 && Get(F,’B’)<10”)             Sums values in actual column in rows that satisfied the condition.

Remember, the ‘&’ and ‘<’ characters are XML entities and must be replaced by &amp; and &lt;

count ( )                Returns count of rows

counta (string col)              Counts all non blank values in the column

countblank (string col)     Counts all blank values in the column

countif (string col, string condition)              Counts all values in column that satisfies the condition, see sumif.

product (string col)            Multiplies all values in column

max (string col)                  Returns maximum value from the column

min (string col)                   Returns minimum value from the column

countrows (int type, string def)      Counts specific count of rows according to type. type is bit array.

                                               type&1 include filtered rows, type&2 include deleted rows, type&4 include all children, not only immediate,

type&8 selected rows only (the count is not updated after selection changes).

If set def, it counts only rows with given default name.

 

Next functions cannot be used for server paging (cannot be pre-calculated)

average (string col)                           Calculates average of column values (sum/count)

median (string col)                            Returns median of column (middle value of range)

mode (string col)                                               Returns modus of column (the most frequent value in range)

avedev (string col)                             Calculates average deviation of column

stdev (string col)                                Calculates standard deviation of column

stdevp (string col)                              Calculates standard deviation of column. column is the selection of data.

vara (string col)                                 Calculates the sample variance of column

varp (string col)                                 Calculates the sample variance of column. column is the selection of data.

rank (string col, object val)             Returns position of val in sorted column (column is sorted ascending). Starting from 0.

 

 

Special functions for actions

 

choose (value, values[], items[],custom)       Returns one item from items on position where values[pos] == value. If no value equals, returns custom.

                If value is null, uses cell value instead. If values is null, tests cell Defaults, if Defaults is also null, uses array [0,1,2,3,...]

                Call with null value and null values if you want to convert cell value from Defaults to another value or index.

                If items is null, returns one item from cell Defaults, if Defaults is also null, returns the index to values. If custom is null, returns cell Custom attribute.

                Call with null items and custom if you want to get item from Defaults according to index or another condition.

                Example: choose (“B”,[“A”,”B”,”C”],[“X”,”Y”,”Z”],”None”) - returns “Y”, because value “B” is on second position in values and “Y” is second in items.

                This function can be used also for Select type cells.

split (value, separator)      Splits value string to array. Items are separated by separator. If value is null, uses cell value instead.

                If separator is null, splits by the first character in value. The first empty item is discarded.

 

 

Gantt chart

Support functions for Gantt chart.

To show Gantt chart in some column set its Type=’Gantt’ and specify other Gantt attributes.

 

ganttpercent (startdatecol, duedatecol, unitround, percentcol)            Calculates summary percent of all child rows for its date ranges

                startdatecol – column name (in quotes) where start dates are stored, for this row and also its children

                duedatecol - column name (in quotes) where due dates are stored, for this row and also its children, due date is the last date in the range

                unitround – time rounding for all dates, possible values are the same as units in gantt function.

                percentcol – column where percent of complete are stored, if omitted the actual column is used

Example: <I Calculated=’1’ CalcOrder=”Complete” CompleteFormula=”ganttpercent(‘StartDate’,’EndDate’,’d’)”/>

 

ganttpercentduration (durationcol, percentcol)       Calculates summary percent of all child rows for its date ranges

                durationcol - column name (in quotes) where due dates are stored, for this row and also its children, due date is the last date in the range

                percentcol – column where percent of complete are stored, if omitted the actual column is used

Example: <I Calculated=’1’ CalcOrder=”Complete” CompleteFormula=”ganttpercent(‘Duration’)”/>

 

ganttduration (startdatecol, durationcol, units)        Calculated summary duration for child rows. Use when the gantt is set by start and duration instead of start and duedate.

                startdatecol – column name (in quotes) where start dates are stored, for this row and also its children

                duedate - column name (in quotes) where durations are stored, for this row and also its children

                units – time units for all dates, possible values are the same as units in gantt function.

Example: <I Calculated=’1’ CalcOrder=”Duration” DurationFormula=”ganttduration(‘StartDate’,’EndDate’,’d’)”/>

 

 

Custom functions

 

You can use any JavaScript statement or function. Evaluation has context of objects TCalc. So you can use its parameters and functions and you can add member functions or properties to TCalc and use them directly. For example, you can reference values from some user configuration or values set in another html input.

All custom functions you must define in script file not in formula itself.

TCalc

TGrid Grid                          Pointer to actual grid where calculation is running.

TRow Row                           Actual row, where the being computed cell lies.

string Col                             Actual column, where the being computed cell lies.

TRow [] Children               All child rows of actual rows. Is filled only if row is Calculated. For fixed row there are all root variable rows. Does not contain deleted rows.

There are also available all aggregate functions and cell values, see paragraph Input values.

For example, simple aggregate count function is defined: TCalc.prototype.count = function(){ return this.Children.length; }

Remember, if you are using server paging and you want use custom functions, you must also prepare bodies on server side by these functions.

 

 

 

 

 

 

 

 

Updates

 

3.3

Updated aggregate functions

Added DebugCalc

Many information were updated and added

 

3.5

Added function countrows

 

4.0.3

Updated countrows function

 

4.4

Added Advanced calc order section

 

5.0

Added Special functions section – choose and split functions

 

5.7

Added gantt and ganttpercent functions

 

5.8

Updated gantt function, added parameters dependencies, dependencytypes, flags, flagsinfo and updated options parameter

 

5.9

Removed gantt function, Gantt chart is now specified by Type=’Gantt’

Added function ganttpercentduration, ganttduration