Formula

Formula is used to define calculation rule, you can define the formula inside the data-formula attribute and write the formula like the you write it in excel. There are a lot excel compatible formula can be used from simple SUM, HLOOKUP, VLOOKUP to complex financial function like NPV, IRR, etc.

Beside the excel compatible formula, there is also custom formula built in jQuery Calx, they are SERVER() and GRAPH()

SERVER()

This is special function to perform calculation in server side, you must define ajaxUrl parameter when configuring calx. The first parameter of this function is the formula name, and the rest is formula parameters

When it invoked, jQuery Calx will send the request to the configured ajaxUrl and wait for the response before continue to the next formula execution.

SERVER('SUM', A1:A3, B4)

Formula above will send request looks like below



[
    function    => 'SUM',
    params      => array(
        /* range from A1 to A3 */
        1   => array(
            A1  => 'value of A1',
            A2  => 'value of A2',
            A3  => 'value of A3'
        ),

        /* the value of B4 */
        2   => 'value of B4'
    )
]

#ERROR_SEND_REQUEST! will be returned when error occured.

GRAPH()

This is special function used to draw graphic or chart to represent the data in graphical way. You need to place the GRAPH formula in the div element with specified height and width.

 

The first parameter is cell range contains data that need to be represented as graphic.
The second parameter is array containing some ‘key=value’ to define how the chart should be rendered.

  • type:
    type could be one of the following bar, line, pie, or doughnut, default is line.
  • label:
    label is used in bar or line type chart to draw label in the x-axis of the chart. If none is given, the label will be incremental number starting from 0.
  • legend:
    legend is used to give explanation on the chart. If none is given, the legend will be blank
  • bar_orientation:
    if the chart type is bar, you can define bar_orientaion, it could be vertical or horizontal. If none is given, the default will be vertical
  • show_x_axis:
    show_x_axis is used to control the appearance of x axis label, it could be true or false. If none is given, default is true.
  • show_y_axis:
    show_y_axis is used to control the appearance of y axis label, it could be true or false. If none is given, default is true.
  • reverse:
    revers is used to control the order of the data, it could be true or false. If none is given, default is false.
  • orientation:
    orientation is used to define the table orientation, it could be vertical or horizontal, default it horizontal
    Horizontal table:
    jQuery Calx will parse single row as single series.

    +-------+-------+-------+-------+-------+
| val 1 | val 2 | val 3 | val 4 | val 5 |
+-------+-------+-------+-------+-------+
| val 1 | val 2 | val 3 | val 4 | val 5 |
+-------+-------+-------+-------+-------+

    Vertical table:
    jQuery Calx will parse single column as single series.

    +-------+-------+
| val 1 | val 1 |
+-------+-------+
| val 2 | val 2 |
+-------+-------+
| val 3 | val 3 |
+-------+-------+
| val 4 | val 4 |
+-------+-------+
| val 5 | val 5 |
+-------+-------+