Sort groups by their aggregates
Environment
Product Version | 2019.3.917 |
Product | Progress® Kendo UI® Grid for jQuery |
Description
I have a Kendo Grid with monthly sales data, grouped by customer. I was trying to sort the data by total sales in ascending order, but I can't find a way to sort the groups based on a specific criteria. I need to sort the groups by their aggregated fields.
Solution
The Kendo UI DataSource group configuration has a compare
function that can be used to sort the groups order:
dir: "asc",
compare: function(a, b) {
var a = a.items.map(x=>x.total);
var b = b.items.map(x=>x.total);
var reducer = (accumulator, currentValue) => accumulator + currentValue;
var aTotalSum = a.reduce(reducer);
var bTotalSum = b.reduce(reducer);
if (aTotalSum == bTotalSum) {
return 0;
} else if (aTotalSum > bTotalSum) {
return 1;
} else {
return -1;
}
}
The following example demonstrates how to apply a custom compare function to sort the groups by their aggregated fields.
<div id="grid"></div>
<script>
var dataSource = new kendo.data.DataSource({
data: [
{ customer: "Walmart", store: "Florida", july: 25, aug: 10, sep: 12, total: 47 },
{ customer: "Target", store: "Manhattan", july: 30, aug: 12, sep: 0, total: 42 },
{ customer: "Walmart", store: "Los Angeles", july: 12, aug: 12, sep: 12, total: 36 },
{ customer: "Walmart", store: "Phoenix", july: 12, aug: 0, sep: 12, total: 24 },
{ customer: "Telerik", store: "Las Vegas", july: 0, aug:0, sep: 12, total: 12 },
{ customer: "Target", store: "Seattle", july: 25, aug: 20, sep: 15, total: 60 },
{ customer: "Telerik", store: "Houston", july: 1, aug: 5, sep:0, total: 6 }
],
pageSize: 10,
schema:{
model: {
fields:{
july: { type: "number"},
aug: { type: "number"},
sep: { type: "number"},
total: { type: "number"}
}
}
},
group: {
field: "customer",
aggregates: [
{ field: "july", aggregate: "sum" },
{ field: "aug", aggregate: "sum" },
{ field: "sep", aggregate: "sum" },
{ field: "total", aggregate: "sum" }
],
dir: "asc",
compare: function(a, b) {
var a = a.items.map(x=>x.total);
var b = b.items.map(x=>x.total);
var reducer = (accumulator, currentValue) => accumulator + currentValue;
var aTotalSum = a.reduce(reducer);
var bTotalSum = b.reduce(reducer);
if (aTotalSum == bTotalSum) {
return 0;
} else if (aTotalSum > bTotalSum) {
return 1;
} else {
return -1;
}
}
},
sort: {
field: "total",
dir: "asc"
}
});
$("#grid").kendoGrid({
dataSource: dataSource,
pageable: true,
columns: [
{
field: "customer",
groupHeaderTemplate: "#=value#",
hidden: true,
},
{
title: "Store",
field: "store",
width: 150,
groupFooterTemplate: "<div style='text-align: right;'>Total:</div>"
},
{
title: "July Sales",
field: "july",
attributes: { style: "text-align: right;" },
groupFooterTemplate: "<div style='text-align: right;'>#= sum || ''#</div>"
},
{
title: "Aug Sales",
field: "aug",
attributes: { style: "text-align: right;" },
groupFooterTemplate: "<div style='text-align: right;'>#= sum || ''#</div>"
},
{
title: "Sep Sales",
field: "sep",
attributes: { style: "text-align: right;" },
groupFooterTemplate: "<div style='text-align: right;'>#= sum || ''#</div>"
},
{
title: "Total Sales",
field: "total",
aggregate: ["sum"],
width: 140,
attributes: { style: "text-align: right;" },
groupFooterTemplate: "<div style='text-align: right;'>Total Sales: #= sum || ''#</div>",
}
]
});
</script>