Skip to main content

DHTMLX Pivot. Custom predicates example

Demo of custom predicates in DHTMLX Pivot. Define grouping logic like month-year date bucketing and profit sign classification to organize columns and rows by custom criteria.

Live example​

const predicates = {
    monthYear: { 
        label: "Month-year",
        type: "date",
        handler: d => new Date(d.getFullYear(), d.getMonth(), 1),
        template: (date, locale) => {
            const months = locale.getRaw().calendar.monthFull;
            return months[date.getMonth()] + " " + date.getFullYear();
        },
    },
    profitSign: {
        label: "Profit Sign",
        type: "number",
        filter: {
            type: "tuple",
            format: v => (v < 0 ? "Negative" : "Positive"),
        },
        field: f => f === "profit",
        handler: v => (v < 0 ? -1 : 1),
        template: v =>
            v < 0 ? "Negative profit" : "Positive profit",
    },
};

// 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,
    predicates: { ...pivot.defaultPredicates, ...predicates },
    tableShape:{ tree:true },
    config: {
        rows: [
            "product_type",  "product"
        ],
        columns: [ 
            { field: "profit", method:"profitSign"},
            { field:"date", method:"monthYear"}
        ],
        values: [
            "sales", 
            "expenses"
        ]
    }
});

<!-- 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>

Standard pivot grouping uses raw field values - each unique value in a column or row field creates a separate group. But many analytical scenarios require derived grouping: dates grouped by month-year rather than individual days, or numeric values categorized as "Positive" vs. "Negative" based on a threshold. Without custom grouping, you would need to preprocess the dataset before passing it to the pivot.

DHTMLX Pivot supports custom predicates that define how values are grouped before aggregation. Each predicate specifies a handler that transforms the raw value into a group key, a template for display formatting, and optional filter configuration. Predicates are merged with pivot.defaultPredicates and referenced in the config.columns or config.rows arrays by name.

This example demonstrates two custom predicates on a coffee sales dataset. The monthYear predicate groups date values by the first day of each month, displaying them as "October 2018", "November 2018", etc. The profitSign predicate classifies profit values as "Negative profit" or "Positive profit". Combined with tree mode, this creates a hierarchical view where products are nested under product types, and columns split by profit sign and then by month.

Solution overview​

To create custom predicates, define an object where each key is the predicate name. Each custom predicate needs a handler to compute the group key and a type matching the field data type, while template is optional for display formatting. Reference predicates in config.columns or config.rows using { field, method } syntax.

Key points​

  • Handler defines grouping: The handler function receives a raw cell value and returns a group key - dates map to month boundaries, numbers map to sign categories (-1 or 1)
  • Template controls display: The template function formats the group key for column/row headers, using locale data for month names or custom strings for categories
  • Filter integration: The profitSign predicate includes a filter configuration with type: "tuple" and a format function, enabling the configuration panel to show human-readable filter options
  • Tree mode compatibility: Custom predicates work seamlessly with tableShape: { tree: true }, creating a nested view where row hierarchies combine with predicate-based column grouping

API reference​

Additional resources​