How to do aggregate sum if numbers displayed are in European format?

[TeeJT]

How to do aggregate sum if numbers displayed are in European format?
Is it possible that the column.format is applied but the array of numbers in aggregate would still have the numbers in their original form - NumberDecimal Separator = "." ?
I noticed all the numbers in the column have been changed to NumberDecimal Separator = "," or European format and agg.sum is gives me NaN (Not a number)

[paramvir]

column.format doesn't change the original cell data, it only displays formatted cells in the grid.

Aggregate is independent of format, so it's done the same usual way.

Please check this example and try changing the format: https://paramquery.com/pro/demos/group_rows

[TeeJT]

In your sample, I used your example at https://paramquery.com/pro/demos/group_rows_custom_summary

Code: [Select]

fnVal = pq.formatNumber( agg.sum(arr, col), col.format);

And the result of agg.sum(arr, col) is NaN

This is the whole coding

Code: [Select]


$(function () {
function fillOptions(grid) {
var column = grid.getColumn({ dataIndx: 'ShipCountry' });
column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
column.filter.cache = null;
grid.refreshHeader();
}
var agg = pq.aggregate;
agg.max1 = function(arr, col) {
var maxNum = 0;
if (col.dataType == "float" || col.dataType == "integer") {
var arr1 = [];
for (var i=0; i<arr.length; i++) {
var item = parseInt(arr[i]);
if (item == NaN) {item = 0;}
arr1.push(item);
}
maxNum = Math.max.apply(null, arr1);
}
if (col.dataType == "date") {

}
return pq.formatNumber(maxNum, col.format);
}

agg.sum_null = function(arr, col) {
return myAgg(arr, col, "sum");
}

function myAgg(arr, col, fnName) {
var ret = "";
var res = fnName.split("_");
for(var i=0; i<res.length; i++) {
txt = "";
if (i>0) {txt = "<BR>";}
var fn = res[i];
var fnVal = 0;
switch (fn) {
case "avg":
fnVal = pq.formatNumber( agg.avg(arr, col), col.format);
fnVal = isNaN(fnVal) ? "" : fnVal;
txt += "Avg: " + fnVal;
break;
case "max":
if ( col.dataType == "date"){
var val1 = agg.max(arr, col);
var timestamp = Date.parse(val1);
var date1 = (isNaN(timestamp)) ? "" : new Date(val1);
txt += "Max: " + $.datepicker.formatDate( col.format, date1);
}
else{
txt += "Max: " + agg.max1(arr, col);
}

break;
case "min":
if ( col.dataType == "date"){
var val1 = agg.min(arr, col);
var timestamp = Date.parse(val1);
var date1 = (isNaN(timestamp)) ? "" : new Date(val1);
txt += "Min: " + $.datepicker.formatDate( col.format, date1);
}
else{
fnVal = pq.formatNumber( agg.min(arr, col), col.format);
fnVal = isNaN(fnVal) ? "" : fnVal;
txt += "Min: " + fnVal;
}
break;
case "sum":
fnVal = pq.formatNumber( agg.sum(arr, col), col.format);
fnVal = isNaN(fnVal) ? "" : fnVal;
txt += "Sum: " + fnVal;
break;
case "count":
txt += "count: " + agg.count(arr, col);
break;
case "var":
fnVal = pq.formatNumber( agg.sum(arr, col), col.format);
fnVal = isNaN(fnVal) ? "" : fnVal;
txt += "Variance: " + fnVal;
break;
}
ret += txt;
}
ret = ret.replace("undefined", "");
ret = ret.replace("NaN", "");
return ret;
}


var colM = [
{ title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
filter: {
type: 'select',
prepend: { '': 'All Countries' },
valueIndx: 'ShipCountry',
labelIndx: 'ShipCountry',
condition: 'equal',
listeners: ['change']
}
},
{ title: "Customer Name", width: 130, dataIndx: "ContactName" },
{ title: "Freight", width: 120, format: '$##.###,00',
summary: {
type: "sum_null"
},
dataType: "float", dataIndx: "Freight"
},
{ title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
{ title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
{ title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
{ title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
var dataModel = {
location: "remote",
dataType: "JSON",
method: "GET",
url: "/Content/orders.json"
//url: "/pro/orders/get",//for ASP.NET
//url: "orders.php",//for PHP
};
var groupModel = {
on: true,
summaryInTitleRow: 'all', //to display summary in the title row.
dataIndx: ['ShipCountry', 'ContactName'],
//showSummary: [true], //to display summary at end of every group.           
title: [
"{0} ({1})",
"{0} - {1}"
]
};
var obj = {
height: 500,
toolbar: {
items: [{
type: 'button',
label: "Toggle grouping",
listener: function () {
this.Group().option({
on: !this.option('groupModel.on')
});
}
}]
},
dataModel: dataModel,
scrollModel: { autoFit: true },
colModel: colM,
numberCell: { show: false },
filterModel: { on: true, header: true, type: "local" },
selectionModel: { type: 'cell' },
groupModel: groupModel,
load: function (evt, ui) {
//options for ShipCountry filter.   
fillOptions(this);
},
showTitle: false,
resizable: true,
virtualX: true,
virtualY: true,
hwrap: false,
wrap: false
};
pq.grid("#grid_group_rows", obj);

});



[TeeJT]

I realize that I can do what I want. I must have done it wrongly. Thanks a lot.


I used your sample at https://paramquery.com/pro/demos/group_rows

This is the code:

Code: [Select]


    $(function () {
        function fillOptions(grid) {
            var column = grid.getColumn({ dataIndx: 'ShipCountry' });
            column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
            column.filter.cache = null;
            grid.refreshHeader();
        }

var agg = pq.aggregate,
format = function(val){
return pq.formatNumber(val, "##.###,00");
}

        //define custom aggregate "all" and now it can be used in any column.
        agg.sum_max_min = function(arr, col){
            return "Sum: " + format( agg.sum(arr, col) )
                +", Max: " + format( agg.max(arr, col) )
                +", Min: " + format( agg.min(arr, col) );
        }


        var colM = [
            { title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
                filter: {
                    type: 'select',
                    prepend: { '': 'All Countries' },
                    valueIndx: 'ShipCountry',
                    labelIndx: 'ShipCountry',
                    condition: 'equal',
                    listeners: ['change']
                }
            },
            { title: "Customer Name", width: 130, dataIndx: "ContactName" },
            { title: "Freight", width: 120, format: '##.###,00',
                summary: {
                    type: "sum_max_min"
                },
                dataType: "float", dataIndx: "Freight"
            },
            { title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
    { title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
            { title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
            { title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
        var dataModel = {
            location: "remote",
            dataType: "JSON",
            method: "GET",
wrap: true,
            url: "/Content/orders.json"
            //url: "/pro/orders/get",//for ASP.NET
            //url: "orders.php",//for PHP
        };
        var groupModel = {
            on: true,
            summaryInTitleRow: 'all', //to display summary in the title row.
            dataIndx: ['ShipCountry', 'ContactName'],
            //showSummary: [true], //to display summary at end of every group.           
            title: [
                "{0} ({1})",
                "{0} - {1}"
            ]
        };
        var obj = {
            height: 500,
            toolbar: {
                items: [{
                    type: 'button',
                    label: "Toggle grouping",
                    listener: function () {
                        this.Group().option({
                            on: !this.option('groupModel.on')
                        });
                    }
                }]
            },
            dataModel: dataModel,
            scrollModel: { autoFit: true },
            colModel: colM,
            numberCell: { show: false },
            filterModel: { on: true, header: true, type: "local" },
            selectionModel: { type: 'cell' },
            groupModel: groupModel,
            load: function (evt, ui) {
                //options for ShipCountry filter.   
                fillOptions(this);
            },
            showTitle: false,
            resizable: true,
            virtualX: true,
            virtualY: true,
            hwrap: false,
            wrap: true
        };
        pq.grid("#grid_group_rows", obj);

    });