Skip to main content

DHTMLX Pivot. Custom math methods example

Demo of custom math methods in DHTMLX Pivot. Define your own aggregation functions like counting unique dates and averaging date values, then merge them with built-in methods.

Live example​

function countUnique(values, converter) {
    const valueMap = {};
    return values.reduce((acc, d) => {
        if (converter) d = converter(d);
        if (!valueMap[d]) {
            acc++;
            valueMap[d] = true;
        }
        return acc;
    }, 0);
}

const methods = {
    countunique_date: {
        handler: values => countUnique(values, v => new Date(v).getTime()),
        type: "date",
        label: "CountUnique",
    },
    average_date: {
        type: "date",
        label: "Average",
        branchMode: "raw",
        handler: values => {
            if (!values.length) return null;
            const sum = values.reduce((acc, d) => acc + d.getTime(), 0);
            const avgTime = sum / values.length;
            return new Date(avgTime);
        },
    },
};

// show integers for "count" and "unique count" results
const templates = {};
fields.forEach(f => {
    if (f.type == "number")
        templates[f.id] = (v, method) =>
            v && method.indexOf("count") < 0 ? parseFloat(v).toFixed(3) : v;
});

// date string to Date 
const dateFields = fields.filter(f => f.type == "date");
if (dateFields.length) {
    dataset.forEach(item => {
        dateFields.forEach(f => {
            const v = item[f.id];
            if (typeof v == "string") item[f.id] = new Date(v);
        });
    });
}

const widget = new pivot.Pivot("#pivot", {
    fields,
    data: dataset,
    tableShape: { templates },
    methods: { ...pivot.defaultMethods, ...methods },
    config: {
        rows: [
            "state"
        ],
        columns: [
            "product_line",
            "product_type"
        ],
        values: [
            {
                field: "date",
                method: "countunique_date"
            },
            {
                field: "date",
                method: "average_date"
            },
        ]
    }
});

<!-- component container -->
<div id="pivot" style="width: 100%; height: 100%;"></div>

<!-- dataset -->

<script>
const dataset = [
      {
        "cogs": 51,
        "date": "10/1/2018",
        "inventory_margin": 503,
        "margin": 71,
        "market_size": "Major Market",
        "market": "Central",
        "marketing": 46,
        "product_line": "Leaves",
        "product_type": "Herbal Tea",
        "product": "Lemon",
        "profit": -5,
        "sales": 122,
        "state": "Colorado",
        "expenses": 76,
        "type": "Decaf"
      },
      {
        "cogs": 52,
        "date": "10/1/2018",
        "inventory_margin": 405,
        "margin": 71,
        "market_size": "Major Market",
        "market": "Central",
        "marketing": 17,
        "product_line": "Leaves",
        "product_type": "Herbal Tea",
        "product": "Mint",
        "profit": 26,
        "sales": 123,
        "state": "Colorado",
        "expenses": 45,
        "type": "Decaf"
      },
      // ... 1060 more items (see Live Editor for full data)
    ];
    const fields = [
      {
        "id": "cogs",
        "label": "Cogs",
        "type": "number"
      },
      {
        "id": "date",
        "label": "Date",
        "type": "date"
      },
      {
        "id": "inventory_margin",
        "label": "Inventory Margin",
        "type": "number"
      },
      {
        "id": "margin",
        "label": "Margin",
        "type": "number"
      },
      {
        "id": "market_size",
        "label": "Market Size",
        "type": "text"
      },
      {
        "id": "market",
        "label": "Market",
        "type": "text"
      },
      {
        "id": "marketing",
        "label": "Marketing",
        "type": "number"
      },
      {
        "id": "product_line",
        "label": "Product Line",
        "type": "text"
      },
      {
        "id": "product_type",
        "label": "Product Type",
        "type": "text"
      },
      {
        "id": "product",
        "label": "Product",
        "type": "text"
      },
      {
        "id": "profit",
        "label": "Profit",
        "type": "number"
      },
      {
        "id": "sales",
        "label": "Sales",
        "type": "number"
      },
      {
        "id": "state",
        "label": "State",
        "type": "text"
      },
      {
        "id": "expenses",
        "label": "Expenses",
        "type": "number"
      },
      {
        "id": "type",
        "label": "Type",
        "type": "text"
      }
    ];
</script>

The built-in aggregation methods in DHTMLX Pivot (sum, count, min, max, etc.) cover most common scenarios, but real-world datasets often require specialized calculations. For instance, you might need to count the number of unique dates across records or compute an average date from a collection of timestamps. These operations have no direct equivalent among the default methods.

DHTMLX Pivot allows you to define custom math methods and merge them with pivot.defaultMethods. Each custom method specifies a handler function that receives the array of cell values and returns the aggregated result, along with a type and label for display. You can also control how branching works with the branchMode property, which determines whether the handler receives raw values or pre-aggregated subtotals.

This example demonstrates two custom methods applied to a coffee sales dataset: countunique_date counts the number of distinct dates using getTime() as a converter, and average_date computes the average date by summing timestamps and dividing by the count. A templates object formats numeric output to three decimal places while keeping count results as integers.

Solution overview​

To add custom math methods, define an object where each key is the method name and the value describes the handler, type, and label. Merge your custom methods with pivot.defaultMethods using the spread operator. Optionally, define templates to control how values are formatted in cells.

Key points​

  • Merge with defaults: Use { ...pivot.defaultMethods, ...methods } to add custom methods alongside built-in ones, so users can still select sum, count, min, max from the configuration panel
  • Handler receives raw values: Each method's handler function gets the array of cell values for the group and must return the aggregated result - a number, date, or string
  • Branch mode control: Set branchMode: "raw" to receive original Date objects instead of pre-aggregated subtotals, which is essential for methods that need to operate on raw data
  • Cell templates for formatting: The tableShape.templates object lets you format aggregated values per field - here, numbers display three decimal places except for count-based results that show integers

API reference​

  • Pivot methods: Configuration for defining custom and built-in aggregation methods.
  • Pivot tableShape: Configuration for table appearance including cell templates.

Additional resources​