Cell formulas description
TreeGrid v5.0
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.
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.
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 !
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.
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 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.
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 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.
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 characters <=32 are ignored except in string constants.
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.
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
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.
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.
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.
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.
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