jQuery plugin: Tablesorter 2.0 - Math Widget

# tablesorter

## Math Widget

### Flexible client-side table sorting

Back to documentation

### Notes

- In v2.19.1, added `math_event` option & fixed an issue with event unbinding in jQuery version < 1.9.
- In v2.17.1,
  - Values added to the data-attribute set by the `textAttribute` option will now be used in the calculation instead of the actual cell content.
  - The Grand Total cells now shows a higher precision value to emphasize this point.
- In v2.16.4, added:
  - Two new options: `math_prefix` and `math_suffix`, which will be added before or after the prefix or suffix, respectively.
  - Added "Mask Examples" section with examples, and how to use the `$.tablesorter.formatMask` function.
- This widget will only work in tablesorter version 2.16+ and jQuery version 1.7+.
- It adds basic math capabilities. A full list of default formulas is listed in the "Attribute Settings" section.
- Add your own custom formulas which manipulating an array of values gathered from the table by row, column or a column block (above).
- This is by no means a comprehensive widget that performs like a spreadsheet, but you can customize the data gathering "type" and available "formula", as desired.
- The widget will update the calculations based on filtered rows, and will update if any data within the table changes (using update events).
- This widget is not optimized for very large tables, for two reasons:
  - On initialization, it cycles through every table row, calculates the column index, and adds a `data-column` attribute.
  - It uses the update method whenever it recalculates values to make the results sortable. This occurs when any of the update methods are used and after the table is filtered.
- When setting tablesorter's `debug` option to `true`, this widget will output each `{type}-{formula}` value found, the array of numbers used and the result.

### Options

### Math widget default options (added inside of tablesorter `widgetOptions`)

TIP! Click on the link in the option column to reveal full details (or toggle|show|hide all) or double click to update the browser location.

Option | Default | Description
--- | --- | ---
math_data | `'math'` | Set this option to point to the named data-attribute. For example, when set to `'math'`, the widget looks for settings within the `data-math` attribute.
math_event | `'recalculate'` | Set this option change the name of the event that the widget listens for to perform an update.

math_ignore

`[ ]`

Set this option the column index of columns of data to ignore.
To ignore the first and second columns in a table, set this option using zero-based column indexs as follows:

    // column index(es) to ignore
    math_ignore : [0,1]

math_mask

`'#,##0.00'`

Set this option with an output formatting mask to use *
As of v2.16.2, you can set a mask for each math cell by adding a `data-math-mask` data-attribute (the `math` part of the data-attribute is obtained from the `math_data` setting).

    <th data-math="all-sum" data-math-mask="##0.00">all-sum</th>

### Javascript-number-formatter details

### Features

- Short, fast, flexible yet standalone. Only 75 lines including MIT license info, blank lines & comments.
- Accept standard number formatting like `#,##0.00` or with negation `-000.####`.
- Accept any country format like `# ##0,00`, `#,###.##`, `#'###.##` or any type of non-numbering symbol.
- Accept any numbers of digit grouping. `#,##,#0.000` or `#,###0.##` are all valid.
- Accept any redundant/fool-proof formatting. `##,###,##.#` or `0#,#00#.###0#` are all OK.
- Auto number rounding.
- Include a prefix & suffix with the mask
- Simple interface, just supply mask & value like this: `$.tablesorter.formatMask( "0.0000", 3.141592, "prefix ", " suffix" );`

      $.tablesorter.formatMask( "$ 0.0000 USD", 1234.567, "prefix ", " suffix" );
      // prefix $ 1234.5670 USD suffix

### Limitation

- No scientific/engineering formatting.
- Not for date or phone formation.
- No color control.
- No prefix or suffix is allowed except leading negation symbol. So `$#,##0.00` or `#,###.##USD` will not yield expected outcome. Use `'$'+ $.tablesorter.formatMask('#,##0.00', 123.45)` or `$.tablesorter.formatMask('#,##0.00', 456.789) + 'USD'`
- The prefix or suffix can not include any numbers (`0-9`), hashes (`#`), dashes (`-`), or plus signs (`+`)

### Note

- When there's only one symbol is supplied, system will always treat the single symbol as Decimal. For instance, `$.tablesorter.formatMask( '#,###', 1234567.890)` will output `1234567,890`.
- To force a single symbol as Separator, add a trailing dot to the end like this: `$.tablesorter.formatMask( '#,###.', 1234567.890)` which will then output `1,234,567`.
- Original plugin demo

* The number formatter code was copied from javascript-number-formatter (MIT) - now forked on GitHub.

math_complete

`null`

This function is called after each calculation is made to allow re-formatting, adding prefixes, suffixes, etc to the result.
Use this option as follows:

    // complete executed after each function
    math_complete : function($cell, wo, result, value, arry){
        return '$ ' + result + $cell.attr('data-suffix');
    }

- `$cell` - the target cell (jQuery object)
- `wo` - tablesorter's widget options (from `table.config.widgetOptions`).
- `result` - the formatted result of the calculation.
- `value` - an unformatted result of the calculation.
- `arry` - the array of values gathered by the widget.

In this function, if a anything is returned, it will be automatically added to the `$cell` as html. Or, return `false` and no change is made to the cell contents; use this method if you manipulate the `$cell` contents and don't want the widget to do it.
If you need to format the data output after manipulating the `value`, you can use `wo.math_mask`, or a different mask, by using the `$.tablesorter.formatMask( mask, value );` function. For example:

    math_complete : function($cell, wo, result, value, arry){
        var percent = Math.round( value * 1e4 ) / 100; // percent with two decimal places
        return $.tablesorter.formatMask( wo.math_mask, percent ) + ' %';
    }

More details can be found in the `math_mask` description.

math_priority

`[ 'row', 'above', 'col' ]`

This is the order of calculations.

- By default, the widget cycles through the calculated cells as follows:
  - Search all non-informational tbodies for `data-math` table cells (data-attribute set by `math_data` option).
  - Cycle through these cells by priority: row, above, col (set by this option).
  - Search all informational tbodies, and `tfoot` for `data-math` table cells.
  - Cycle through these cells by priority: row, above, col (set by this option).
  - Search the entire table for `data-math` cells of the "all" type.
- So, all row calculations are made first, followed by "above" calculations then "col" (column) calculations.
- The "all" type calculations are always performed last, and therefore the type is not included in this list.
- Change this order if the order of calculations needs to be made column first, followed by rows.
- For more details about the differences between "col" and "above" types, see the next section.

math_prefix

`''`

Add content before the value formatted by the `math_mask` option (v2.16.4).

- This option adds content before the mask.
- If the mask includes any content for the prefix, then this value is added before it.
- If this option contains a `{content}` tag, the prefix within the mask will replace this tag.

      math_mask   : '$ #,##0.00',
      math_prefix : '{content}'

      // result of the value 12345.67
      // <span class="blue">$ </span>12,345.67

  You can experiment with this option in the "Mask Examples" section.

math_suffix

`''`

Add content after the value formatted by the `math_mask` option (v2.16.4).

- This option adds content after the mask.
- If the mask includes any content for the suffix, then this value is added after it.
- If this option contains a `{content}` tag, the suffix within the mask will replace this tag.

      math_mask   : '#,##0.00 USD',
      math_suffix : '{content}!'

      // result of the value 12345.67
      // 12,345.67 USD<span class="red">!</span>

  You can experiment with this option in the "Mask Examples" section.

### Attribute Settings

The math widget data-attibute setting requires two parts: type & formula

    <td data-math="{type}-{formula}"></td>

When set, the data is gathered based on the math type ("row", "column", "above" or "all") and passed to the formula as an array.

### `{type}` (data gathering)

- `row` - gather the table cell values from the same row as the `data-math` attribute.
- `above` - gather the table cell values from the same column as the `data-math` attribute, but stop when the first table cell is reached, or when another cell with a data-attribute with an "above" type is reached; see the first table demo below to see why this is useful.
- `col` - gather the table cell values from the same column as the `data-math` attribute.
- `all` - gather all table cell values with a data-math attribute that start with "all".

### `{formula}` (defaults)

- `count` - returns the count (length) of the data set.
- `sum` - returns the sum of all values in the data set.
- `max` - returns the maximum value in the data set.
- `min` - returns the minimum values in the data set.
- `mean` - returns the mean (average) of all values in the data set; it uses the `sum` formula in part of the calculation.
- `median` - returns the median (middle value) of the data set.
- `mode` - returns an array of the mode(s) (most frequent value or values) in the data set; an array is always returned, even if only one mode exists (see the second demo below).
- `range` - returns the range (highest minus lowest value) of the data set.
- `varp` - returns the variance of the data set (population).
- `vars` - returns the variance of the data set (sample).
- `stdevp` - returns the standard deviation of the data set (population).
- `stdevs` - returns the standard deviation of the data set (sample).
- `custom` (not a default)
  - Custom formulas can have any name
  - Return your result after making whatever calculation from the array of data passed to the formula
  - For example:

        // adding a custom equation... named "product"
        // access from data-math="row-product" (or "above-product", or "col-product")
        $.tablesorter.equations['product'] = function(arry) {
            // multiple all array values together
            var product = 1;
            $.each(arry, function(i,v){
                // oops, we shouldn't have any zero values in the array
                if (v !== 0) {
                    product *= v;
                }
            });
            return product;
        };

#### Ignoring cells

- Entire row: if the `<tr>` math data-attribute contains the keyword `"ignore"` then that entire row of cells will be skipped when building the array of data to be used for calculations.

      <tr data-math="ignore"><td>1</td><td>2</td><td>3</td></tr>

- Cell: if the table cell math data-attribute contains the keyword `"ignore"` then that cell will be skipped when building the array of data to be used for calculations.

      <td data-math="ignore">1</td>

- Column: set the widget `math_ignore` option with an array of zero-based column indexes of columns to ignore or skip when building the array of data for calculations.

      math_ignore : [0,1]

### Mask Examples

The formatting function can be used separately from the math widget:

    // $.tablesorter.formatMask(mask, value, prefix, suffix);
    $.tablesorter.formatMask('$#,##0.00 USD', 12345.678, 'prefix ', ' suffix');
    // result: "prefix $12,345.68 USD suffix"

- The `$.tablesorter.formatMask` function has the following parameters:
  - `mask` - please refer to the `math_mask` option for more details.
  - `value` - number to be formatted.
  - `prefix` - please refer to the `math_prefix` option for more details (v2.16.4).
  - `suffix` - please refer to the `math_suffix` option for more details (v2.16.4).

#### Experiment with the mask:

$#,##0.00 USD ##.000,00 ¥ £-##¿000$00 xx test:### ###. ing prefix with a comma, ok? #.00 yep! prefix with a periods? ok?... #.00 yep! prefix with spaces ##^000*00 suffix, with comma prefix (#,###.00) suffix BAD => mask starts with a decimal .00 <= BAD BAD => mask starts with a comma ,##.00 <= BAD BAD => No hash (#) outside of mask $#,###.00 No # in suffix <= BAD BAD => No plus (+) here! #,###.00 ¢ or [+] here <= BAD BAD => No minus (-) here! $#,###.00 or [-] here either <= BAD

Value to use:
Prefix: ( add `{content}` to include the mask prefix )
Suffix: ( add `{content}` to include the mask suffix )

# Demo

### Row & Column Sums

Region | Salesman | FastCar | RapidZoo | SuperGlue | Grand Total
--- | --- | --- | --- | --- | ---
Column Totals | col-sum | col-sum | col-sum | col-sum
Grand Total | all-sum
Middle | Joseph | $ 423 | $ 182 | $ 255 | row-sum
Middle | Lawrence | $ 5,908 | $ 4,642 | $ 4,593 | row-sum
Middle | Maria | $ 6,502 | $ 3,969 | $ 5,408 | row-sum
Middle | Matt | $ 4,170 | $ 6,093 | $ 5,039 | row-sum
Middle Total | above-sum | above-sum | above-sum | above-sum
North | Joseph | $ 3,643 | $ 5,846 | $ 6,574 | row-sum
North | Lawrence | $ 4,456 | $ 6,658 | $ 7,685 | row-sum
North | Maria | $ 6,235 | $ 4,616.99 | $ 3,612.33 | row-sum
North | Matt | $ 3,868 | $ 3,926 | $ 3,254 | row-sum
North Total | above-sum | above-sum | above-sum | above-sum
West | Joseph | $ 5,507 | $ 5,186 | $ 4,882 | row-sum
West | Lawrence | $ 4,082 | $ 5,272 | $ 6,124 | row-sum
West | Maria | $ 5,520 | $ 5,461 | $ 4,872 | row-sum
West | Matt | $ 6,737 | $ 4,598 | $ 4,233 | row-sum
West Total | above-sum | above-sum | above-sum | above-sum

### Math Formulas

Formula | A | B | C | D | E | F | Result (expected result)
--- | --- | --- | --- | --- | --- | --- | ---
Default Formulas
Count (row-count) | 10 | 10 | 10 | 10 | 20 | 20 |
Sum (row-sum) | 10 | 20 | 10 | 10 | 30 | 20 |
Max (row-max) | 20 | 60 | 30 | 15 | 30 | 5 |
Min (row-min) | 20 | 60 | 30 | 15 | 30 | 5 |
Mean (row-mean) | 10 | 20 | 30 | 10 | 30 | 20 |
Median (row-median) | 10 | 5 | 3 | 4 | 4 | 3 |
Mode (row-mode) | 1 | 2 | 2 | 2 | 3 | 2 |
Mode (row-mode) | 1 | 2 | 2 | 1 | 3 | 4 |
Range (row-range) | 1 | -2 | 2 | 4 | 6 | 0 |
Variance [population] (row-varp) | 2 | 7 | 4 | 5 | 5 | 4 |
Standard Deviation [population] (row-stdevp) | 2 | 7 | 4 | 5 | 5 | 4 |
Variance [sample] (row-vars) | 2 | 7 | 4 | 5 | 5 | 4 |
Standard Deviation [sample] (row-stdevs) | 2 | 7 | 4 | 5 | 5 | 4 |
Custom Formulas
Custom ( (A+B+C)*D - (E/F)*100 ) | 5 | 2 | 3 | 20 | 1 | 2 |
Product ( A*B*C*D*E*F ) | 1 | 2 | 3 | 4 | 5 | 10 |

# Javascript

# CSS

# HTML