I have been experimenting with msg.ui_control to construct a table of profit and loses. I have been able to do addition of individual columns but now I want to calculate net profit/loss which involves dividing the sum of one column with another, which i am not able to accomplish.
In this image I want to do the following maths operation and show it below the Total Profit% column.
1 Like
Hi.
I think you have to write your own calculation function. As you have access to the complete table data it should be doable.
Yes I have seen that but I am not able to get it working. Any pointer towards that would be helpful
Perhaps you can post the relevant part of your flow to make it easier to give you the right starting point
I am experimenting with custom calculation functions, will update soon, In the meantime how can I change the background color of a cell in accordance to the value? I want to change the color of a cell to red if the numeric value is negative and green if positive.
Hi,
you can an example how to change the background (or any other css attribute) here: Ui-table row change background color on updateorAddData - #4 by dceejay
Another good starting point how to work with ui_control is the exampe 6:
How can I set the property of each column and sub column using msg.ui_control?
Simple By sending ui_control.tabulator messages. I can‘t see or experienced any limitations.
And these messages accumulate. So you can keep your basic configuration (in the config dialog) and add additional parameters to them. For the column array: When the property = field parameter matches it will be added to a specific column. See example 6
Basically my expertise is in mechanical engineering, thats why I am creating whole sort of such problems. Here in this case I am passing this ui_control msg
{
"tabulator": {
"minHeight": 600,
"autoColumns": true,
"columnHeaderVertAlign": "middle",
"hozAlign": "center",
"columns": [
{
"title": "Fund Name",
"field": "Fund Name",
"formatter": "plaintext",
"align": "center",
"width": 250,
"headerVertical": true
},
{
"title": "SIP Info",
"formatter": "plaintext",
"hozAlign": "center",
"align": "center",
"columns": [
{
"title": "SIP Amount",
"field": "SIP Amount",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "SIP Date",
"field": "SIP Date",
"formatter": "plaintext",
"align": "center",
"headerVertical": true
}
]
},
{
"title": "Day Returns",
"formatter": "plaintext",
"align": "center",
"columns": [
{
"title": "Amount",
"field": "Day Change",
"property": "dayreturns",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "Percentage",
"field": "Day Change %",
"property": "dayreturns%",
"formatter": "plaintext",
"align": "center",
"headerVertical": true
}
]
},
{
"title": "Monthly Returns",
"formatter": "plaintext",
"align": "center",
"columns": [
{
"title": "Amount",
"field": "Month Change",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "Percentage",
"field": "Month Change %",
"formatter": "plaintext",
"align": "center",
"headerVertical": true
}
]
},
{
"title": "Investment",
"formatter": "plaintext",
"align": "center",
"columns": [
{
"title": "Total Value",
"field": "Total Value",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "Invested",
"field": "Invested",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
}
]
},
{
"title": "Total Returns",
"formatter": "plaintext",
"align": "center",
"columns": [
{
"title": "Amount",
"field": "Total Profit",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "Percentage",
"field": "Total Profit %",
"formatter": "plaintext",
"align": "center",
"headerVertical": true
}
]
}
]
},
"height": "600px"
}
And as in the example 6, I am using a button node to sent rowFormatter topic messages to this with the following payload.
function(row){ if(row.getData().dayreturns>0){ row.getElement().style.backgroundColor = "#32CD32"; } else { row.getElement().style.backgroundColor = "#DC143C"; } },
I am expecting the rows of table with dayreturn field less than 0 to be red or else green. But all the table changes to red.
If there are sub columns under a column, how will the property be defined?, I mean if I am not specifically mentioning the property payload in the msg.ui_control
Sorry for the delay (I had a busy week)
You will need a callback function as described in the docs. Your function will look like this:
var totalPercentage = function(values, data, calcParams){
var sumOfTotalInvested = 0;
var sumOfTotalProfit = 0;
data.forEach((row) => {
sumOfTotalInvested += row["Total Invested"];
sumOfTotalProfit += row["Total Profit"];
});
console.log({ sumOfTotalInvested, sumOfTotalProfit});
return sumOfTotalInvested / sumOfTotalProfit / 100;
}
I use a unconnected function node to write these. Then I copy the function (without var totalPercentage = into the correct parameter (in your case buttomCalc). I use the visual editor as eliminating new line and escaping inverted commas is done there automatically:
Simply
CTRL+A and paste.
as you already define your complete table via one ui_control message you can simply add it here. I haven't tested the accumulated updates for nested columns but in your case you don't need that.
Hope this helps (I simplified your definition ... I hope you get the point.)
a little tip: if possible avoid spaces and special caracters as field parameters. "field":"totalProfitPercent" is better as you can then write row.totalProfitPercentinstead of row["Total Profit %"];
[{"id":"483a640b4504e770","type":"ui_table","z":"d2952ff54c89c139","group":"11346c8e6b90e622","name":"","order":0,"width":"10","height":"7","columns":[],"outputs":0,"cts":false,"x":470,"y":220,"wires":[]},{"id":"67f4ade2fbe62d03","type":"inject","z":"d2952ff54c89c139","name":"ui_control","props":[{"p":"ui_control","v":"{\"tabulator\":{\"columns\":[{\"title\":\"Fund Name\",\"field\":\"Fund Name\",\"formatter\":\"plaintext\",\"align\":\"center\",\"width\":250,\"headerVertical\":true},{\"title\":\"Total Returns\",\"formatter\":\"plaintext\",\"align\":\"center\",\"columns\":[{\"title\":\"Invested\",\"field\":\"Total Invested\",\"formatter\":\"plaintext\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"sum\"},{\"title\":\"Profit\",\"field\":\"Total Profit\",\"formatter\":\"plaintext\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"sum\"},{\"title\":\"Percentage\",\"field\":\"Total Profit %\",\"formatter\":\"plaintext\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"function(values, data, calcParams){ var sumOfTotalInvested = 0; var sumOfTotalProfit = 0; data.forEach((row) => { sumOfTotalInvested += row[\\\"Total Invested\\\"]; sumOfTotalProfit += row[\\\"Total Profit\\\"]; }); console.log({ sumOfTotalInvested, sumOfTotalProfit}); return sumOfTotalInvested / sumOfTotalProfit / 100; }\"}]}]},\"customHeight\":12}","vt":"json"},{"p":"payload"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payloadType":"str","x":280,"y":220,"wires":[["483a640b4504e770","2f98e4576c386d48"]]},{"id":"48f50f8d64bb5b9e","type":"inject","z":"d2952ff54c89c139","name":"Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Fund Name\":\"Line 2\",\"Total Invested\":50000,\"Total Profit\":5000,\"Total Profit %\":10},{\"Fund Name\":\"Line 3\",\"Total Invested\":50000,\"Total Profit\":1000,\"Total Profit %\":5},{\"Fund Name\":\"Line 4\",\"Total Invested\":50000,\"Total Profit\":2000,\"Total Profit %\":5},{\"Fund Name\":\"Line 5\",\"Total Invested\":50000,\"Total Profit\":6000,\"Total Profit %\":7},{\"Fund Name\":\"Line 6\",\"Total Invested\":50000,\"Total Profit\":11000,\"Total Profit %\":12}]","payloadType":"json","x":290,"y":260,"wires":[["483a640b4504e770"]]},{"id":"2f98e4576c386d48","type":"debug","z":"d2952ff54c89c139","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":470,"y":260,"wires":[]},{"id":"1a7d2840b336fccd","type":"function","z":"d2952ff54c89c139","name":"callback functions","func":"var totalPercentage = function(values, data, calcParams){\n var sumOfTotalInvested = 0;\n var sumOfTotalProfit = 0;\n data.forEach((row) => {\n sumOfTotalInvested += row[\"Total Invested\"];\n sumOfTotalProfit += row[\"Total Profit\"];\n });\n \n console.log({ sumOfTotalInvested, sumOfTotalProfit});\n\n return sumOfTotalInvested / sumOfTotalProfit / 100;\n}\n\nvar testCalc = function(values, data, calcParams){\n debugger;\n return 123;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":690,"y":220,"wires":[[]]},{"id":"11346c8e6b90e622","type":"ui_group","name":"Calculation Functions","tab":"e33f9653df8ec25d","order":1,"disp":true,"width":"10","collapse":true},{"id":"e33f9653df8ec25d","type":"ui_tab","name":"Table","icon":"dashboard","disabled":false,"hidden":false}]
1 Like
Thanks bro, Sorted Everything Out, I really appreciate your help.
I need help with one more small thing. i am currently using the following payload
function(row){ if(row.getData().DayChange>0){ row.getElement().style.backgroundColor = "#096B00"; } else { row.getElement().style.backgroundColor = "#DC143C"; } },
with the topic rowFormatter to colorize the entire rows red if the value of the element in column DayChange of that column is negative and green if positive. This is working fine but the problem is that there are other columns such as MonthlyChange and all.
I am thinking of a method to check each element in the columns that i want and colorize that cell, red if negative, green if positive. Hope you can help.
This works simmilar to a row formatter .. here you need a cell formatter:
var cellFormatter = function(cell, formatterParams, onRendered){
let value = cell.getValue();
if (value > formatterParams.threshold) {
cell.getElement().style.backgroundColor = formatterParams.aboveColor;
} else {
cell.getElement().style.backgroundColor = formatterParams.belowColor;
}
return value + ((formatterParams.unit) ? ` ${formatterParams.unit}` : '');
}
You can either use a individual callback for every column or use the formatterParams object to use the same code and tweak the parameters for every column. This way it is easier to edit your json
[{"id":"483a640b4504e770","type":"ui_table","z":"d2952ff54c89c139","group":"11346c8e6b90e622","name":"","order":0,"width":"10","height":"7","columns":[],"outputs":0,"cts":false,"x":470,"y":220,"wires":[]},{"id":"67f4ade2fbe62d03","type":"inject","z":"d2952ff54c89c139","name":"ui_control","props":[{"p":"ui_control","v":"{\"tabulator\":{\"columns\":[{\"title\":\"Fund Name\",\"field\":\"Fund Name\",\"formatter\":\"plaintext\",\"align\":\"center\",\"width\":250,\"headerVertical\":true},{\"title\":\"Total Returns\",\"formatter\":\"plaintext\",\"align\":\"center\",\"columns\":[{\"title\":\"Invested\",\"field\":\"Total Invested\",\"formatter\":\"plaintext\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"sum\"},{\"title\":\"Profit\",\"field\":\"Total Profit\",\"formatterParams\":{\"threshold\":2000,\"aboveColor\":\"#900000\",\"belowColor\":\"#009000\",\"unit\":\"$\"},\"formatter\":\"function(cell, formatterParams, onRendered){ let value = cell.getValue(); if (value > formatterParams.threshold) { cell.getElement().style.backgroundColor = formatterParams.aboveColor; } else { cell.getElement().style.backgroundColor = formatterParams.belowColor; } return value + ((formatterParams.unit) ? ` ${formatterParams.unit}` : ''); }\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"sum\"},{\"title\":\"Percentage\",\"field\":\"Total Profit %\",\"formatterParams\":{\"threshold\":10,\"aboveColor\":\"#DC143C\",\"belowColor\":\"#096B00\",\"unit\":\"%\"},\"formatter\":\"function(cell, formatterParams, onRendered){ let value = cell.getValue(); if (value > formatterParams.threshold) { cell.getElement().style.backgroundColor = formatterParams.aboveColor; } else { cell.getElement().style.backgroundColor = formatterParams.belowColor; } return value + ((formatterParams.unit) ? ` ${formatterParams.unit}` : ''); }\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"function(values, data, calcParams){ var sumOfTotalInvested = 0; var sumOfTotalProfit = 0; data.forEach((row) => { sumOfTotalInvested += row[\\\"Total Invested\\\"]; sumOfTotalProfit += row[\\\"Total Profit\\\"]; }); console.log({ sumOfTotalInvested, sumOfTotalProfit}); return sumOfTotalInvested / sumOfTotalProfit / 100; }\"}]}]},\"customHeight\":12}","vt":"json"},{"p":"payload"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payloadType":"str","x":280,"y":220,"wires":[["483a640b4504e770","2f98e4576c386d48"]]},{"id":"48f50f8d64bb5b9e","type":"inject","z":"d2952ff54c89c139","name":"Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Fund Name\":\"Line 2\",\"Total Invested\":50000,\"Total Profit\":5000,\"Total Profit %\":10},{\"Fund Name\":\"Line 3\",\"Total Invested\":50000,\"Total Profit\":1000,\"Total Profit %\":5},{\"Fund Name\":\"Line 4\",\"Total Invested\":50000,\"Total Profit\":2000,\"Total Profit %\":5},{\"Fund Name\":\"Line 5\",\"Total Invested\":50000,\"Total Profit\":6000,\"Total Profit %\":7},{\"Fund Name\":\"Line 6\",\"Total Invested\":50000,\"Total Profit\":11000,\"Total Profit %\":12}]","payloadType":"json","x":290,"y":260,"wires":[["483a640b4504e770"]]},{"id":"2f98e4576c386d48","type":"debug","z":"d2952ff54c89c139","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":470,"y":260,"wires":[]},{"id":"1a7d2840b336fccd","type":"function","z":"d2952ff54c89c139","name":"callback functions","func":"var totalPercentage = function(values, data, calcParams){\n var sumOfTotalInvested = 0;\n var sumOfTotalProfit = 0;\n data.forEach((row) => {\n sumOfTotalInvested += row[\"Total Invested\"];\n sumOfTotalProfit += row[\"Total Profit\"];\n });\n \n console.log({ sumOfTotalInvested, sumOfTotalProfit});\n\n return sumOfTotalInvested / sumOfTotalProfit / 100;\n}\n\nvar testCalc = function(values, data, calcParams){\n debugger;\n return 123;\n}\n\nvar cellFormatter = function(cell, formatterParams, onRendered){\n let value = cell.getValue();\n if (value > formatterParams.threshold) {\n cell.getElement().style.backgroundColor = formatterParams.aboveColor; \n } else { \n cell.getElement().style.backgroundColor = formatterParams.belowColor; \n } \n\n return value + ((formatterParams.unit) ? ` ${formatterParams.unit}` : '');\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":690,"y":220,"wires":[[]]},{"id":"11346c8e6b90e622","type":"ui_group","name":"Calculation Functions","tab":"e33f9653df8ec25d","order":1,"disp":true,"width":"10","collapse":true},{"id":"e33f9653df8ec25d","type":"ui_tab","name":"Table","icon":"dashboard","disabled":false,"hidden":false}]
1 Like
This worked, Thanks
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
