Aggregate math

[fauziMember]

i need help for aggregate problem, i need to calculate between aggregate.  here is my code for aggregate

aggregates: [{ ‘Growth’:
function (aggregatedValue, currentValue, column, record) {
var valgrowth =  (record[‘jml’]-record[‘jmlbefore’])/record[‘jmlbefore’] *100;
return  aggregatedValue + valgrowth ;
}
}] }

but it got wrong calculate.

for example (thousandsseparator=’.’, decimalseparator=’,’)

sum for column jml = 3.413.032.763.733

sum for column jmlbefore = 3.063.254.021.527

then the value in aggregate Growth = 167,43 it should be 11,41

aggregates: [{ ‘Total’:
function (aggregatedValue, currentValue, column, record) {
var persen =  (record[‘jml’] – record[‘jmlbefore’])/record[‘jmlbefore’];
return  aggregatedValue + persen ;
}
}] }

[DimitarParticipant]

Hello fauzi,

Please provide us with a larger code sample, including your whole grid initialization. Also, please clarify what you actually mean by calculation between aggregates.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[fauziMember]

thanks for your reply.
this is my whole grid initialization :

$(document).ready(function () {
var theme = “”;

var data = ‘[{“nourut”:1,”useridx”:”734520347″,”nama”:”USMAN”,”jml”:”437269516879″,”jmlbefore”:”356540725633″,”jmltdkrutin”:”5014559642″,”jmlrutin”:432254957237,”pert”:22.6422356388},{“nourut”:2,”useridx”:”734522561″,”nama”:”RARA”,”jml”:”309275146817″,”jmlbefore”:”199788317838″,”jmltdkrutin”:”10613034533″,”jmlrutin”:298662112284,”pert”:54.8014169016},{“nourut”:3,”useridx”:”060089412″,”nama”:”ADI”,”jml”:”265207852888″,”jmlbefore”:”234637083669″,”jmltdkrutin”:”6738664094″,”jmlrutin”:258469188794,”pert”:13.0289589101},{“nourut”:4,”useridx”:”734520052″,”nama”:”KURNIAWAN”,”jml”:”243709355674″,”jmlbefore”:”203845528697″,”jmltdkrutin”:”5215190395″,”jmlrutin”:238494165279,”pert”:19.5558996225},{“nourut”:5,”useridx”:”965412943″,”nama”:”KURNIANTO”,”jml”:”229441664462″,”jmlbefore”:”211395529633″,”jmltdkrutin”:”1796352196″,”jmlrutin”:227645312266,”pert”:8.53666814068},{“nourut”:6,”useridx”:”887490408″,”nama”:”ELIZABETH”,”jml”:”229005625720″,”jmlbefore”:”223492342188″,”jmltdkrutin”:”205225824″,”jmlrutin”:228800399896,”pert”:2.46687804961},{“nourut”:7,”useridx”:”882200140″,”nama”:”DEAZY”,”jml”:”206900171515″,”jmlbefore”:”201821286508″,”jmltdkrutin”:”2222260040″,”jmlrutin”:204677911475,”pert”:2.51652592988},{“nourut”:8,”useridx”:”968588133″,”nama”:”PANCORO”,”jml”:”206375133064″,”jmlbefore”:”170347271530″,”jmltdkrutin”:”8293449293″,”jmlrutin”:198081683771,”pert”:21.1496557652},{“nourut”:9,”useridx”:”809420354″,”nama”:”DIAN”,”jml”:”184635595707″,”jmlbefore”:”149897547350″,”jmltdkrutin”:”1010761547″,”jmlrutin”:183624834160,”pert”:23.1745275164},{“nourut”:10,”useridx”:”734521219″,”nama”:”AGUS”,”jml”:”175870151246″,”jmlbefore”:”143119313613″,”jmltdkrutin”:”8485358091″,”jmlrutin”:167384793155,”pert”:22.8835904856},{“nourut”:11,”useridx”:”734521281″,”nama”:”YUDHI, S.S.T”,”jml”:”138133744826″,”jmlbefore”:”157531122160″,”jmltdkrutin”:”982917872″,”jmlrutin”:137150826954,”pert”:-12.3133619999},{“nourut”:12,”useridx”:”760483267″,”nama”:”BAMBANG”,”jml”:”136811089329″,”jmlbefore”:”167837514742″,”jmltdkrutin”:”14949190604″,”jmlrutin”:121861898725,”pert”:-18.4859895362},{“nourut”:13,”useridx”:”763084924″,”nama”:”PAHMA”,”jml”:”121426316771″,”jmlbefore”:”64618772368″,”jmltdkrutin”:”1188540647″,”jmlrutin”:120237776124,”pert”:87.9118285929},{“nourut”:14,”useridx”:”760112892″,”nama”:”MUHAMMAD”,”jml”:”120124621045″,”jmlbefore”:”107461503204″,”jmltdkrutin”:”1738242345″,”jmlrutin”:118386378700,”pert”:11.7838644198},{“nourut”:15,”useridx”:”816720201″,”nama”:”ANITA”,”jml”:”117554128464″,”jmlbefore”:”74782163601″,”jmltdkrutin”:”122932566″,”jmlrutin”:117431195898,”pert”:57.1954097119},{“nourut”:16,”useridx”:”734522879″,”nama”:”BUDI”,”jml”:”96068993481″,”jmlbefore”:”89645511534″,”jmltdkrutin”:”692776698″,”jmlrutin”:95376216783,”pert”:7.16542505819},{“nourut”:17,”useridx”:”734520331″,”nama”:”ARI”,”jml”:”89039777025″,”jmlbefore”:”81463540002″,”jmltdkrutin”:”196703331″,”jmlrutin”:88843073694,”pert”:9.30015688345},{“nourut”:18,”useridx”:”801751150″,”nama”:”ETIK”,”jml”:”84807750061″,”jmlbefore”:”70505311676″,”jmltdkrutin”:”2149944760″,”jmlrutin”:82657805301,”pert”:20.2856182676},{“nourut”:19,”useridx”:”000000000″,”nama”:”UNKNOWN”,”jml”:”21376128759″,”jmlbefore”:”154522427407″,”jmltdkrutin”:”0″,”jmlrutin”:21376128759,”pert”:-86.1663260682},{“nourut”:20,”useridx”:”734521384″,”nama”:”JOHN ARFIANTO”,”jml”:”0″,”jmlbefore”:”1208174″,”jmltdkrutin”:”0″,”jmlrutin”:0,”pert”:-100}]’;

// prepare the data
var source =
{
datatype: “json”,
datafields: [
{ name: ‘nourut’, type: ‘int’ },
{ name: ‘useridx’, type: ‘string’ },
{ name: ‘nama’, type: ‘string’ },
{ name: ‘jml’, type: ‘number’ },
{ name: ‘jmlbefore’, type: ‘number’},
{ name: ‘jmlrutin’, type: ‘number’},
{ name: ‘jmltdkrutin’, type: ‘number’},
{ name: ‘pert’, type: ‘number’ }
],
localdata: data

};
var cellclass = function (row, columnfield, value) {
if (value < 0) {
return 'red';
}
}

var cellsrendererid = function (row, columnfield, value, defaulthtml, columnproperties) {
var iduseridxar = $('#jqxgrid').jqxGrid('getrowdata', row).useridx;
return '‘ + value + ”;
};

var cellsrenderer1 = function (row, columnfield, value, defaulthtml, columnproperties) {
var iduseridxar = $(‘#jqxgrid’).jqxGrid(‘getrowdata’, row).useridx;
if (columnproperties.cellsformat != ”) {
if ($.jqx.dataFormat) {
if ($.jqx.dataFormat.isDate(value)) {
formatedValue = $.jqx.dataFormat.formatdate(value, columnproperties.cellsformat);
}
else if ($.jqx.dataFormat.isNumber(value)) {
formatedValue = $.jqx.dataFormat.formatnumber(value, columnproperties.cellsformat, getLocalization());
}
}
}
return ‘‘ + formatedValue + ‘‘;
};

var cellsrenderer2 = function (row, columnfield, value, defaulthtml, columnproperties) {
var iduseridxar = $(‘#jqxgrid’).jqxGrid(‘getrowdata’, row).useridx;
if (columnproperties.cellsformat != ”) {
if ($.jqx.dataFormat) {
if ($.jqx.dataFormat.isDate(value)) {
formatedValue = $.jqx.dataFormat.formatdate(value, columnproperties.cellsformat);
}
else if ($.jqx.dataFormat.isNumber(value)) {
formatedValue = $.jqx.dataFormat.formatnumber(value, columnproperties.cellsformat, getLocalization());
}
}
}
return ‘‘ + formatedValue + ‘‘;
};

var cellsrenderer3 = function (row, columnfield, value, defaulthtml, columnproperties) {
var iduseridxar = $(‘#jqxgrid’).jqxGrid(‘getrowdata’, row).useridx;
if (columnproperties.cellsformat != ”) {
if ($.jqx.dataFormat) {
if ($.jqx.dataFormat.isDate(value)) {
formatedValue = $.jqx.dataFormat.formatdate(value, columnproperties.cellsformat);
}
else if ($.jqx.dataFormat.isNumber(value)) {
formatedValue = $.jqx.dataFormat.formatnumber(value, columnproperties.cellsformat, getLocalization());
}
}
}
return ‘‘ + formatedValue + ‘‘;
};

var cellsrenderer4 = function (row, columnfield, value, defaulthtml, columnproperties) {
var iduseridxar = $(‘#jqxgrid’).jqxGrid(‘getrowdata’, row).useridx;
if (columnproperties.cellsformat != ”) {
if ($.jqx.dataFormat) {
if ($.jqx.dataFormat.isDate(value)) {
formatedValue = $.jqx.dataFormat.formatdate(value, columnproperties.cellsformat);
}
else if ($.jqx.dataFormat.isNumber(value)) {
formatedValue = $.jqx.dataFormat.formatnumber(value, columnproperties.cellsformat, getLocalization());
}
}
}
return ‘‘ + formatedValue + ‘‘;
};

var dataAdapter = new $.jqx.dataAdapter(source);

var getLocalization = function () {
var localizationobj = {};
localizationobj.currencysymbol = “Rp “;
localizationobj.decimalseparator = “,”;
localizationobj.thousandsseparator = “.”;
return localizationobj;
}

$(“#jqxgrid”).jqxGrid(
{
width: 1100,
source: dataAdapter,
theme: theme,
sortable:true,
localization: getLocalization(),
autoheight: true,
showstatusbar: true,
statusbarheight: 30,
showaggregates: true,
altrows: true,
enabletooltips: true,
enablehover: true,
columns: [
{ text: ‘NO’, align: ‘center’, datafield: ‘nourut’, width: 60, cellsalign:’center’ },
{ text: ‘IDUSER’, align: ‘center’, datafield: ‘useridx’, width: 80, cellsrenderer:cellsrendererid },
{ text: ‘NAMA’, align: ‘center’, datafield: ‘nama’, minwidth: 150 },
{ text: ‘2012’, align: ‘center’, datafield: ‘jmlbefore’, width: 180, cellsalign: ‘right’, cellsformat:’f’, aggregates: [‘sum’], cellsrenderer:cellsrenderer1 },
{ text: ‘RUTIN’, columngroup: ‘thnpilih’, align: ‘center’, datafield: ‘jmlrutin’, width: 150, cellsalign: ‘right’, cellsformat:’f’, aggregates: [‘sum’], cellsrenderer:cellsrenderer2 },
{ text: ‘TDK RUTIN’, columngroup: ‘thnpilih’, align: ‘center’, datafield: ‘jmltdkrutin’, width: 150, cellsalign: ‘right’, cellsformat:’f’, aggregates: [‘sum’], cellsrenderer:cellsrenderer3 },
{ text: ‘JML 2013’, columngroup: ‘thnpilih’, align: ‘center’, datafield: ‘jml’, width: 180, cellsalign: ‘right’, cellsformat:’f’, aggregates: [‘sum’], cellsrenderer:cellsrenderer4 },
{ text: ‘GROWTH 2013’, align: ‘center’, datafield: ‘pert’, width: 180, cellsalign: ‘right’, cellsformat:’f2′, cellclassname: cellclass,
aggregates: [{ ‘Total Growth’:
function (aggregatedValue, currentValue, column, record) {
var persen = (record[‘jml’] – record[‘jmlbefore’])/record[‘jmlbefore’]*100;
return aggregatedValue + persen ;
}
}] }
],
columngroups: [
{ text: ‘2013’, align: ‘center’, name: ‘thnpilih’ }
]
});

$(“#csvExport”).jqxButton({ theme: theme });
$(“#htmlExport”).jqxButton({ theme: theme });

$(“#csvExport”).click(function () {
$(“#jqxgrid”).jqxGrid(‘exportdata’, ‘csv’, ‘ata-perbulanpercsv’);
});
$(“#htmlExport”).click(function () {
$(“#jqxgrid”).jqxGrid(‘exportdata’, ‘html’, ‘ata-perbulanperhtml’);
});
});

.red:not(.jqx-grid-cell-hover):not(.jqx-grid-cell-selected), .jqx-widget .red:not(.jqx-grid-cell-hover):not(.jqx-grid-cell-selected) {
color: red;
}

it will output aggregate :
for column 2012 -> sum:3.063.254.021.527
for column JML 2013 -> sum:3.413.032.763.733
for column GROWTH 2013 -> Total Growth:167,43 (it is not right, because ((3.413.032.763.733-3.063.254.021.527)/3.063.254.021.527)*100 is 11,41)
that what i mean. sory for my english.

[DimitarParticipant]

Hi fauzi,

The issue comes not from an incorrect summing, but from a mathematical misunderstanding:

the sum of all the 20 “growths” (last column’s aggregated value = 167,43) is not equal to the “growth” between the two sums of all 20 rows (11,41).

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[Author]

Posts