Calculate average from two payloads

General
1

Hi,

Im trying to calculate an average amount of counts each minute that the machine is on. I got the total amount of counts and the total time the machine is on. Now i tried to calculate the average of those to with a function deviding both payloads but this didn't work.

[{"id":"436ccf76.76ba9","type":"ui_text","z":"1f216cc0.281a93","group":"ef52ec43.10754","order":20,"width":"6","height":"1","name":"","label":"Aantal producten:","format":"{{msg.payload}}","layout":"row-spread","x":890,"y":1180,"wires":[]},{"id":"5724ec23.7bf4e4","type":"ui_text","z":"1f216cc0.281a93","group":"ef52ec43.10754","order":6,"width":"4","height":"2","name":"","label":"Aantal slagen","format":"{{msg.payload}}","layout":"row-spread","x":880,"y":1140,"wires":[]},{"id":"addd1c61.c2031","type":"debug","z":"1f216cc0.281a93","name":"Count","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":850,"y":1100,"wires":[]},{"id":"2fb3f952.f74bd6","type":"debug","z":"1f216cc0.281a93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":850,"y":1220,"wires":[]},{"id":"6f27a22.1211f5c","type":"counter","z":"1f216cc0.281a93","name":"","init":"0","step":"1","lower":"","upper":"","mode":"increment","outputs":2,"x":660,"y":1180,"wires":[["5724ec23.7bf4e4","addd1c61.c2031","436ccf76.76ba9"],["2fb3f952.f74bd6"]]},{"id":"c82113ff.98093","type":"debug","z":"1f216cc0.281a93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":230,"y":1220,"wires":[]},{"id":"2e0cb0c6.2888a","type":"mqtt in","z":"1f216cc0.281a93","name":"","topic":"pers77_slag","qos":"2","broker":"d0e4f151.5ec18","x":70,"y":1180,"wires":[["6f27a22.1211f5c","c82113ff.98093"]]},{"id":"7d41b144.805ae","type":"inject","z":"1f216cc0.281a93","name":"","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":1940,"wires":[["ec2b5a1.7dc6da8"]]},{"id":"ec2b5a1.7dc6da8","type":"function","z":"1f216cc0.281a93","name":"Select statement","func":"msg.topic = \"select SUM(`DATA_WAARDE`/1000/60) AS minuutaan FROM `IOT_DATA` WHERE (`DATA_SOORT`= 'AAN') AND (`DATA_TIJD` >= CURDATE())\";\nreturn msg;","outputs":1,"noerr":0,"x":310,"y":1940,"wires":[["dedb1be.784fde8"]]},{"id":"dedb1be.784fde8","type":"mysql","z":"1f216cc0.281a93","mydb":"23c1ad96.82d712","name":"","x":530,"y":1940,"wires":[["a117dd0.dafa62","7b757624.af06a8"]]},{"id":"7b757624.af06a8","type":"change","z":"1f216cc0.281a93","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].minuutaan","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":1940,"wires":[["e247461.f78cab8"]]},{"id":"e247461.f78cab8","type":"ui_text","z":"1f216cc0.281a93","group":"ef52ec43.10754","order":12,"width":"6","height":"2","name":"","label":"Minuten aan:","format":"{{msg.payload}}","layout":"row-spread","x":950,"y":1940,"wires":[]},{"id":"a117dd0.dafa62","type":"debug","z":"1f216cc0.281a93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":950,"y":1980,"wires":[]},{"id":"ef52ec43.10754","type":"ui_group","z":"","name":"Pers 77","tab":"6b96da3f.ce1c04","order":1,"disp":true,"width":"24","collapse":false},{"id":"d0e4f151.5ec18","type":"mqtt-broker","z":"","name":"MQTT_Broker","broker":"192.168.16.76","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"23c1ad96.82d712","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"VEKO_IOT","tz":""},{"id":"6b96da3f.ce1c04","type":"ui_tab","z":"","name":"Machine data","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

Example: (text output) Aantal slagen text/ (text output) Minuten aan:

I also tried it with cont- average node but this didn't work aswell.

Any idea/ tips how i can do this?

2

Where are you doing the math and where are you getting the total time from? in the flow you provided those do not show up.

3

Sorry, the total time is below in the flow. Here is the latest one

[{"id":"567d98af.b9d5e8","type":"ui_text","z":"1cc053f8.1b681c","group":"ef52ec43.10754","order":20,"width":"6","height":"1","name":"","label":"Aantal producten:","format":"{{msg.payload}}","layout":"row-spread","x":1050,"y":1100,"wires":[]},{"id":"c6e9011b.7bd3c","type":"ui_text","z":"1cc053f8.1b681c","group":"ef52ec43.10754","order":6,"width":"4","height":"2","name":"","label":"Aantal slagen","format":"{{msg.payload}}","layout":"row-spread","x":1040,"y":1060,"wires":[]},{"id":"46c635ad.5f028c","type":"debug","z":"1cc053f8.1b681c","name":"Count","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1010,"y":1020,"wires":[]},{"id":"bf94a47f.b80ce8","type":"debug","z":"1cc053f8.1b681c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1010,"y":1140,"wires":[]},{"id":"eae75827.808388","type":"counter","z":"1cc053f8.1b681c","name":"","init":"0","step":"1","lower":"","upper":"","mode":"increment","outputs":2,"x":820,"y":1100,"wires":[["c6e9011b.7bd3c","46c635ad.5f028c","567d98af.b9d5e8","277a0d6a.b17252"],["bf94a47f.b80ce8"]]},{"id":"d34aeb1.81bc218","type":"debug","z":"1cc053f8.1b681c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":390,"y":1140,"wires":[]},{"id":"93da82e8.7e34a","type":"mqtt in","z":"1cc053f8.1b681c","name":"","topic":"pers77_slag","qos":"2","broker":"d0e4f151.5ec18","x":230,"y":1100,"wires":[["eae75827.808388","d34aeb1.81bc218"]]},{"id":"a1e58e7e.75f55","type":"inject","z":"1cc053f8.1b681c","name":"","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"onceDelay":0.1,"x":230,"y":1320,"wires":[["4bf3a6f4.4d72f8"]]},{"id":"4bf3a6f4.4d72f8","type":"function","z":"1cc053f8.1b681c","name":"Select statement","func":"msg.topic = \"select SUM(`DATA_WAARDE`/1000/60) AS minuutaan FROM `IOT_DATA` WHERE (`DATA_SOORT`= 'AAN') AND (`DATA_TIJD` >= CURDATE())\";\nreturn msg;","outputs":1,"noerr":0,"x":450,"y":1320,"wires":[["e0eb53e2.4ac08"]]},{"id":"e0eb53e2.4ac08","type":"mysql","z":"1cc053f8.1b681c","mydb":"23c1ad96.82d712","name":"","x":670,"y":1320,"wires":[["f0cf52c4.7833a","2b6bf60c.74b24a"]]},{"id":"2b6bf60c.74b24a","type":"change","z":"1cc053f8.1b681c","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].minuutaan","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":900,"y":1320,"wires":[["b9906ce4.67c91","3cc43634.f691da"]]},{"id":"b9906ce4.67c91","type":"ui_text","z":"1cc053f8.1b681c","group":"ef52ec43.10754","order":12,"width":"6","height":"2","name":"","label":"Minuten aan:","format":"{{msg.payload}}","layout":"row-spread","x":1090,"y":1320,"wires":[]},{"id":"f0cf52c4.7833a","type":"debug","z":"1cc053f8.1b681c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1090,"y":1360,"wires":[]},{"id":"277a0d6a.b17252","type":"change","z":"1cc053f8.1b681c","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.count","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1040,"y":1200,"wires":[["3cc43634.f691da"]]},{"id":"3cc43634.f691da","type":"function","z":"1cc053f8.1b681c","name":"avg calculation","func":"msg.topic = msg.payload[0].minuutaan/msg.payload.count\nreturn msg;","outputs":1,"noerr":0,"x":1280,"y":1200,"wires":[["84b8704f.d22a3","c700af3f.e7951"]]},{"id":"c700af3f.e7951","type":"debug","z":"1cc053f8.1b681c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1460,"y":1200,"wires":[]},{"id":"84b8704f.d22a3","type":"ui_gauge","z":"1cc053f8.1b681c","name":"","group":"d8191a13.e67b18","order":0,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{value}}","min":0,"max":10,"colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":1450,"y":1240,"wires":[]},{"id":"ef52ec43.10754","type":"ui_group","z":"","name":"Pers 77","tab":"6b96da3f.ce1c04","order":1,"disp":true,"width":"24","collapse":false},{"id":"d0e4f151.5ec18","type":"mqtt-broker","z":"","name":"MQTT_Broker","broker":"192.168.16.76","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"23c1ad96.82d712","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"VEKO_IOT","tz":""},{"id":"d8191a13.e67b18","type":"ui_group","z":"","name":"Default","tab":"","disp":true,"width":"6","collapse":false},{"id":"6b96da3f.ce1c04","type":"ui_tab","z":"","name":"Machine data","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
4

Ok the function node
15%20AM
is going to get one msg from the top set msg.payload and it will process it. It will also get a msg from the bottom set msg.payload and process it. This processing will be done at two different times.

You need to either use a join node to join the contents of the two msgs or store one of the values in a context variable which can be used later.

This has come up a lot recently - if you search the forum using 'join' you will see many discuessions

If you want to have it triggered by both input flows, I would have the change nodes store the data in flow variables and then the function node would get the data from the flow variables (https://nodered.org/docs/writing-functions#storing-data) to do the calculation and report it. That way you would always be using the latest data from each input.

5

Ok, maybe an other way. Is it possible to inject every 1minute the total amount of counts into my database?

[{"id":"addd1c61.c2031","type":"debug","z":"1f216cc0.281a93","name":"Count","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":850,"y":1100,"wires":[]},{"id":"c9ca1390.a832d","type":"function","z":"1f216cc0.281a93","name":"INSERT statement","func":"msg.topic = \"INSERT INTO `IOT_DATA` ( `DATA_BRON`,`DATA_SOORT`,`DATA_WAARDE`) VALUES ('77','materiaal',\" + msg.payload + \")\";\nreturn msg;","outputs":1,"noerr":0,"x":950,"y":980,"wires":[["9bbe19c9.c49238","8dbcfd34.6124c"]]},{"id":"8dbcfd34.6124c","type":"debug","z":"1f216cc0.281a93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1150,"y":980,"wires":[]},{"id":"9bbe19c9.c49238","type":"mysql","z":"1f216cc0.281a93","mydb":"23c1ad96.82d712","name":"","x":1150,"y":1020,"wires":[[]]},{"id":"87ed8bde.3abe28","type":"inject","z":"1f216cc0.281a93","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":760,"y":980,"wires":[["c9ca1390.a832d","addd1c61.c2031"]]},{"id":"5724ec23.7bf4e4","type":"ui_text","z":"1f216cc0.281a93","group":"ef52ec43.10754","order":6,"width":"4","height":"2","name":"","label":"Aantal slagen","format":"{{msg.payload}}","layout":"row-spread","x":880,"y":1140,"wires":[]},{"id":"436ccf76.76ba9","type":"ui_text","z":"1f216cc0.281a93","group":"ef52ec43.10754","order":20,"width":"6","height":"1","name":"","label":"Aantal producten:","format":"{{msg.payload}}","layout":"row-spread","x":890,"y":1180,"wires":[]},{"id":"2fb3f952.f74bd6","type":"debug","z":"1f216cc0.281a93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":850,"y":1220,"wires":[]},{"id":"6f27a22.1211f5c","type":"counter","z":"1f216cc0.281a93","name":"","init":"0","step":"1","lower":null,"upper":null,"mode":"increment","outputs":2,"x":660,"y":1180,"wires":[["5724ec23.7bf4e4","addd1c61.c2031","436ccf76.76ba9"],["2fb3f952.f74bd6"]]},{"id":"c82113ff.98093","type":"debug","z":"1f216cc0.281a93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":230,"y":1220,"wires":[]},{"id":"2e0cb0c6.2888a","type":"mqtt in","z":"1f216cc0.281a93","name":"","topic":"pers77_slag","qos":"2","broker":"d0e4f151.5ec18","x":70,"y":1180,"wires":[["6f27a22.1211f5c","c82113ff.98093"]]},{"id":"23c1ad96.82d712","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"VEKO_IOT","tz":""},{"id":"ef52ec43.10754","type":"ui_group","z":"","name":"Pers 77","tab":"6b96da3f.ce1c04","order":1,"disp":true,"width":"24","collapse":false},{"id":"d0e4f151.5ec18","type":"mqtt-broker","z":"","name":"MQTT_Broker","broker":"192.168.16.76","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"6b96da3f.ce1c04","type":"ui_tab","z":"","name":"Machine data","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

Then i calculate the average with sql.

6

Got it with trigger :slight_smile:

7

Here is how to do it with the flow variables so eithor input will recalculate the average.

[{"id":"206e9195.de6a46","type":"ui_text","z":"9c4e0fe1.b76018","group":"206e9b0.9ab15e6","order":20,"width":"6","height":"1","name":"","label":"Aantal producten:","format":"{{msg.payload}}","layout":"row-spread","x":910,"y":140,"wires":[]},{"id":"9869a91c.8de518","type":"ui_text","z":"9c4e0fe1.b76018","group":"206e9b0.9ab15e6","order":6,"width":"4","height":"2","name":"","label":"Aantal slagen","format":"{{msg.payload}}","layout":"row-spread","x":900,"y":100,"wires":[]},{"id":"1ad16f52.364c41","type":"debug","z":"9c4e0fe1.b76018","name":"Count","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":870,"y":60,"wires":[]},{"id":"f9f0c5d9.ddbb08","type":"debug","z":"9c4e0fe1.b76018","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":870,"y":180,"wires":[]},{"id":"20bde557.eaac8a","type":"counter","z":"9c4e0fe1.b76018","name":"","init":"0","step":"1","lower":"","upper":"","mode":"increment","outputs":2,"x":460,"y":140,"wires":[["9869a91c.8de518","1ad16f52.364c41","206e9195.de6a46","cbedb4bf.8cee1"],["f9f0c5d9.ddbb08"]]},{"id":"afb8afb3.676d2","type":"debug","z":"9c4e0fe1.b76018","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":90,"y":200,"wires":[]},{"id":"d9408201.f39cd","type":"mqtt in","z":"9c4e0fe1.b76018","name":"","topic":"pers77_slag","qos":"2","broker":"84b34dd8.319a2","x":90,"y":140,"wires":[["20bde557.eaac8a","afb8afb3.676d2"]]},{"id":"cabd81a9.21fef8","type":"inject","z":"9c4e0fe1.b76018","name":"","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":360,"wires":[["a673beb2.b23518"]]},{"id":"a673beb2.b23518","type":"function","z":"9c4e0fe1.b76018","name":"Select statement","func":"msg.topic = \"select SUM(`DATA_WAARDE`/1000/60) AS minuutaan FROM `IOT_DATA` WHERE (`DATA_SOORT`= 'AAN') AND (`DATA_TIJD` >= CURDATE())\";\nreturn msg;","outputs":1,"noerr":0,"x":290,"y":360,"wires":[["851bb92b.f58b88"]]},{"id":"851bb92b.f58b88","type":"mysql","z":"9c4e0fe1.b76018","mydb":"a992b446.44898","name":"","x":490,"y":360,"wires":[["d880c447.bf0ef","96bdfc67.e29c2"]]},{"id":"96bdfc67.e29c2","type":"change","z":"9c4e0fe1.b76018","name":"set flow.minuutaan","rules":[{"t":"set","p":"minuutaan","pt":"flow","to":"payload[0].minuutaan","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"payload[0].minuutaan","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":470,"y":300,"wires":[["2d5d44b1.2548f4","da669518.122728"]]},{"id":"2d5d44b1.2548f4","type":"ui_text","z":"9c4e0fe1.b76018","group":"206e9b0.9ab15e6","order":12,"width":"6","height":"2","name":"","label":"Minuten aan:","format":"{{msg.payload}}","layout":"row-spread","x":770,"y":340,"wires":[]},{"id":"d880c447.bf0ef","type":"debug","z":"9c4e0fe1.b76018","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":770,"y":380,"wires":[]},{"id":"cbedb4bf.8cee1","type":"change","z":"9c4e0fe1.b76018","name":"","rules":[{"t":"set","p":"count","pt":"flow","to":"count","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":460,"y":220,"wires":[["da669518.122728"]]},{"id":"da669518.122728","type":"function","z":"9c4e0fe1.b76018","name":"avg calculation","func":"var pers_count = flow.get(\"count\");\nvar minuutaan  = flow.get(\"minuutaan\");\n\nmsg.payload = minuutaan/msg.count\nreturn msg;","outputs":1,"noerr":0,"x":700,"y":260,"wires":[["27a4bfd3.a24fe8","6e6598ae.648a58"]]},{"id":"6e6598ae.648a58","type":"debug","z":"9c4e0fe1.b76018","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1050,"y":220,"wires":[]},{"id":"27a4bfd3.a24fe8","type":"ui_gauge","z":"9c4e0fe1.b76018","name":"","group":"c390d8f.7bd6628","order":0,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{value}}","min":0,"max":10,"colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":1030,"y":280,"wires":[]},{"id":"206e9b0.9ab15e6","type":"ui_group","z":"","name":"Pers 77","tab":"6e6538b5.f114d8","order":1,"disp":true,"width":"24","collapse":false},{"id":"84b34dd8.319a2","type":"mqtt-broker","z":"","name":"MQTT_Broker","broker":"192.168.16.76","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"a992b446.44898","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"VEKO_IOT","tz":""},{"id":"c390d8f.7bd6628","type":"ui_group","z":"","name":"Default","tab":"","disp":true,"width":"6","collapse":false},{"id":"6e6538b5.f114d8","type":"ui_tab","z":"","name":"Machine data","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
1 Like