Assuming a webix datatable with 3 columns, with the 3rd column containing a math (division) operation on the other 2 columns (e.g. [$r, -2] / [$r, -1] ). Because of the division operation I can’t use the standard summColumn operation in the footer, I need to force the math operation to be recalculated on the column totals. I implemented a solution, but what is your recommended way to achieve this?
you can try to create custom summColumn and use it in math column
webix.ui.datafilter.totalColumn = webix.extend({
refresh: function (master, node, value) {
var result = 0, _val;
master.data.each(function (obj) {
if (obj.$group) return;
_val = /*implement your logic*/ obj.SOME_COL / obj.OTHER_COL;
if (!isNaN(_val)) result += _val;
});
if (value.format)
result = value.format(result);
if (value.template)
result = value.template({ value: result });
node.firstChild.style.textAlign = "right";
node.firstChild.innerHTML = result;
}
}, webix.ui.datafilter.summColumn);
That is actually my current solution, but I’m not happy with it.
For 2 reasons:
- this fires at every row, and I need to do this only once, on the column totals.
- I have to write custom code to parse the math expression (the [$, -1] syntax etc) and evaluate it. Rather than letting the standard webix component do it.
Is there a better way?
did you try to add math column from scheme->$init? in this case you can use summColumn. if table is editable, you will have to recalc math value after edit.
could you give me an example? The table is not editable but its columns and data are dynamic. Whenever columns contain a math expression (often referencing other columns), I would like that to be applied to footer totals in a similar way to how math expressions operate for individual table rows. That way I could remove my custom code.
Thanks, but this doesn’t use a math expression. Given a math expression ( [$r, -2] / [$r, -1] ) how would I apply that to the footer?
Sorry, a typo in my last post. I meant that if a column contains a webix match expressions such as [$r, :fld1] / [$r, :fld2] I would like that math expression to be applied against the datatable’s footer as well as the datatable’s rows, without having to resort to custom code.
It seems that I’m missing somethog, but the default logic of the summColumn counter will return the needed data.
I mean, if the 3rd column contains data calculated on the base of other two columns, the summColumn counter will sum the results of this division. And it will be updated on each edit: http://webix.com/snippet/c2b393f7
hi Helga, the summColumn counter will perform a SUM of values in a column. But when that column contains a math expression on other columns this may lead to mathematically incorrect results, particularly with division operations. In your example, the grand totals for the columns are:
Revenue 11167000
Expenditures 13988
Deficit/surplus 8328.257 (= the sum of all rows)
However, that is mathematically incorrect. The real total Deficit/surplus is 11167000 / 13988 = 798.327.
So, ideally it should be possible to set a property on the datatable column collection that triggers a column math expression to be ALSO applied to the footer totals (as well as to the individual data rows).
I appreciate this is somewhat of an edge case, and I have already implemented my own workaround, but I wanted to let you know anyway.
I see your point. However, the best solution would be implementing custom logic in the filter. You can pass ids of the needed columns, “rev” and “exp”, to the filter and calculate the correct value:
footer:{content:'totalColumn', columns:["rev", "exp"]}
hi Helga,
Thank you for doing this. This is indeed very close to the custom code I implemented. I just wanted to make sure there wasn’t another more generic way.