Line chart with multiple unknown series

Dashboard
1

Hi

I am able to display multiple lines in a line chart from data coming from a database when i already know what different series will be coming in (by manually adding the different series that are in the database to the flow) to get a line chart like this.

graph ex
graph ex742Ă—362 16.7 KB

But i am unsure how to (or if its possible) to automatically format the data when i do not know what series will be coming from the database (new ones constantly added / too many to manually input in the flow).

This is how my data looks from the database after some formting, the 6 digit number is a production number each different one needs to be its own line, and its value is the days total production of all productions.
payload ex

2

So for ID 505050 you have 3 points, x = 1,2 or 3 minutes past 10 on 1st April and y = 1,2 or 3?

You need to add 505050 to the series array and the x,y points as an array within the data array.

Each array of points will be plotted as a separate line.

3

the X axis is for time and Y for total production from that machine each day (the 1,2,3,4,5). But i need the line split up into separate productions numbers throughout the day (to easily visualize stops, time spent on single productions though out the day etc)

i understand that the data needs to be formatted into an array, but i dont know how to do it (or if its possible) with constantly changing production numbers, the list is far too long to put each and everyone into a function and new ones are added/removed to the database almost daily.

this is an example of the raw data pulled from the database

graph ex2

the days total could easily go to 300+ daily with multiple different production numbers

4

Can you post text not images.

Can we have a example of incoming raw data and how it would be formatted for the chart .

Then we can give you examples that correspond to you data and output format.

5

This is the example payload from the database

[{"LogDateTime":"2023-04-01T10:01:02.003Z","DayCounter":1,"ProductionNumber":505050},{"LogDateTime":"2023-04-01T10:02:02.003Z","DayCounter":2,"ProductionNumber":505050},{"LogDateTime":"2023-04-01T10:03:02.003Z","DayCounter":3,"ProductionNumber":505050},{"LogDateTime":"2023-04-01T10:10:02.003Z","DayCounter":4,"ProductionNumber":505066},{"LogDateTime":"2023-04-01T10:11:02.003Z","DayCounter":5,"ProductionNumber":505066}]```
6

You only provided half what I asked for.
What about the output format you wish that data to be?

Guessing output

[{"id":"1443affe2a15c0ed","type":"inject","z":"da8a6ef0b3c9a5c8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"LogDateTime\":\"2023-04-01T10:01:02.003Z\",\"DayCounter\":1,\"ProductionNumber\":505050},{\"LogDateTime\":\"2023-04-01T10:02:02.003Z\",\"DayCounter\":2,\"ProductionNumber\":505050},{\"LogDateTime\":\"2023-04-01T10:03:02.003Z\",\"DayCounter\":3,\"ProductionNumber\":505050},{\"LogDateTime\":\"2023-04-01T10:10:02.003Z\",\"DayCounter\":4,\"ProductionNumber\":505066},{\"LogDateTime\":\"2023-04-01T10:11:02.003Z\",\"DayCounter\":5,\"ProductionNumber\":505066}]","payloadType":"json","x":150,"y":2780,"wires":[["a4041c0857047ac8"]]},{"id":"a4041c0857047ac8","type":"change","z":"da8a6ef0b3c9a5c8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t    $series := $distinct( $$.payload.ProductionNumber);\t    [\t        {\t           \"series\": $series,\t           \"data\": $series@$k.[\t                [\t                    $$.payload[ProductionNumber = $k].{\"x\": $.LogDateTime, \"y\":$.DayCounter}\t                ]\t            ],\t            \"labels\": $series\t        }\t    ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":2740,"wires":[["a91123f4e56d6d55"]]},{"id":"a91123f4e56d6d55","type":"debug","z":"da8a6ef0b3c9a5c8","name":"debug 223","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":550,"y":2780,"wires":[]}]

{
    "series": [
        505050,
        505066
    ],
    "data": [
        [
            {
                "x": "2023-04-01T10:01:02.003Z",
                "y": 1
            },
            {
                "x": "2023-04-01T10:02:02.003Z",
                "y": 2
            },
            {
                "x": "2023-04-01T10:03:02.003Z",
                "y": 3
            }
        ],
        [
            {
                "x": "2023-04-01T10:10:02.003Z",
                "y": 4
            },
            {
                "x": "2023-04-01T10:11:02.003Z",
                "y": 5
            }
        ]
    ],
    "labels": [
        505050,
        505066
    ]
}
7

Makes sense.

How can the total production from machine 505066 on 1st April 2023 be both 4 and 5?
Did machine 505066 make 1 part at 10:10:02 and one more at 10:11:02? If so, it's misleading to describe this as total production for the day.

Why are your timestamps exactly 1 minute apart?
Why are the timestamps in the future?
Why do the total production values increment by exactly 1 per minute?

It would be easier to advise you if you posted real data instead.

8

its to show at what time each part was made, what that part was and what number that part was in the daily total

no this is not real data, just examples of dummy data i have been given to work with, real data is not being logged yet.

9

I like them pixies...
A side question though: Is the labels array actually used for stored data charts?
I can't see any difference if I set labels = ["foo", "bar"]

10

correct this is how i can put it into a chart. but i dont know how to dynamically set the series that comes from production numbers

11

I think in earlier versions both where displayed in the reading pop up, Now it does not matter
@dapadk look at my example it is fully dynamic.
The dynamic JSONata expression

(
    $series := $distinct( $$.payload.ProductionNumber);
    [
        {
           "series": $series,
           "data": $series@$k.[
                [
                    $$.payload[ProductionNumber = $k].{"x": $.LogDateTime, "y":$.DayCounter}
                ]
            ],
            "labels": $series
        }
    ]
)
1 Like
12

sorry i missed it in the first post.

this works perfectly. i dont really understand how it works but ill be able to use it for this AND more!

thank you very much!!

13

You aren't by any chance making parts for the aerospace industry?

I took the liberty of extrapolating your test data to see what @E1cid's excellent code comes up with.
505066 doesn't get started immediately but then works steadily.
505050 has a 10 minute period when it produces nothing:

[{"id":"1443affe2a15c0ed","type":"inject","z":"134e3f08233c7beb","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":220,"wires":[["ebf178cea972b2c8"]]},{"id":"a4041c0857047ac8","type":"change","z":"134e3f08233c7beb","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t    $series := $distinct( $$.payload.ProductionNumber);\t    [\t        {\t           \"series\": $series,\t           \"data\": $series@$k.[\t                [\t                    $$.payload[ProductionNumber = $k].{\"x\": $.LogDateTime, \"y\":$.DayCounter}\t                ]\t            ],\t            \"labels\": []\t        }\t    ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":460,"y":220,"wires":[["21123f8a19da0927"]]},{"id":"ebf178cea972b2c8","type":"function","z":"134e3f08233c7beb","name":"function 28","func":"msg.payload = [{ \"LogDateTime\": \"2023-04-06T10:01:02.003Z\", \"DayCounter\": 1, \"ProductionNumber\": 505050 },\n{ \"LogDateTime\": \"2023-04-06T10:02:02.003Z\", \"DayCounter\": 2, \"ProductionNumber\": 505050 },\n{ \"LogDateTime\": \"2023-04-06T10:03:02.003Z\", \"DayCounter\": 3, \"ProductionNumber\": 505050 },\n{ \"LogDateTime\": \"2023-04-06T10:24:02.003Z\", \"DayCounter\": 4, \"ProductionNumber\": 505050 },\n{ \"LogDateTime\": \"2023-04-06T10:24:41.003Z\", \"DayCounter\": 5, \"ProductionNumber\": 505050 },\n{ \"LogDateTime\": \"2023-04-06T10:25:23.003Z\", \"DayCounter\": 6, \"ProductionNumber\": 505050 },\n{ \"LogDateTime\": \"2023-04-06T10:25:44.003Z\", \"DayCounter\": 7, \"ProductionNumber\": 505050 },\n{ \"LogDateTime\": \"2023-04-06T10:26:09.003Z\", \"DayCounter\": 8, \"ProductionNumber\": 505050 },\n{ \"LogDateTime\": \"2023-04-06T10:26:41.003Z\", \"DayCounter\": 9, \"ProductionNumber\": 505050 },\n    { \"LogDateTime\": \"2023-04-06T10:10:30.003Z\", \"DayCounter\": 1, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:11:31.003Z\", \"DayCounter\": 2, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:12:33.003Z\", \"DayCounter\": 3, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:13:35.003Z\", \"DayCounter\": 4, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:14:40.003Z\", \"DayCounter\": 5, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:15:44.003Z\", \"DayCounter\": 6, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:16:42.003Z\", \"DayCounter\": 7, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:17:40.003Z\", \"DayCounter\": 8, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:18:30.003Z\", \"DayCounter\": 9, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:19:31.003Z\", \"DayCounter\": 10, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:20:32.003Z\", \"DayCounter\": 11, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:21:33.003Z\", \"DayCounter\": 12, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:22:33.003Z\", \"DayCounter\": 13, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:23:32.003Z\", \"DayCounter\": 14, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:24:31.003Z\", \"DayCounter\": 15, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:25:30.003Z\", \"DayCounter\": 16, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:26:29.003Z\", \"DayCounter\": 17, \"ProductionNumber\": 505066 },\n    { \"LogDateTime\": \"2023-04-06T10:27:27.003Z\", \"DayCounter\": 18, \"ProductionNumber\": 505066 },\n]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":220,"wires":[["a4041c0857047ac8"]]},{"id":"21123f8a19da0927","type":"ui_chart","z":"134e3f08233c7beb","name":"","group":"3231577618700483","order":2,"width":"20","height":"8","label":"chart","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#ff8000","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":610,"y":220,"wires":[[]]},{"id":"3231577618700483","type":"ui_group","name":"Demo","tab":"12fb331e96d4e554","order":1,"disp":true,"width":"20","collapse":false,"className":""},{"id":"12fb331e96d4e554","type":"ui_tab","name":"Demo","icon":"dashboard","disabled":false,"hidden":false}]

Untitled 4
Untitled 41200Ă—537 25.3 KB

Is this the sort of graph you envisage?

You don't say how many different production numbers there are, but note that by default only 35 different line colours are available.

@E1cid : My first try creating my own data only had values for 505050. This resulted in an error "Series not array". Can your Jsonata be tweeked to fix this?

14

This should fix your error

(
    $series := $distinct( $$.payload.ProductionNumber);
    [
        {
           "series": [$series],
           "data": $series[]@$k.[
                [
                    $$.payload[ProductionNumber = $k].{"x": $.LogDateTime, "y":$.DayCounter}
                ]
            ],
            "labels": [$series]
        }
    ]
)

[edit] fixed expression to handle the single ProductNumber

15

Nope, that produces a blank chart (data is an array when it should be an array of arrays)

I'm not sure if JSONata is more like a language cf AWK or a subset of a language cf Regular Expressions. It's well cryptic enough for either!

If only the Change node dropdown allowed us to insert AWK or Perl syntax :thinking:

16

~~~ Please supply your data, as it works for me. ~~~

scratch that i see now it is a single array, not an array of arrays.

[{"id":"1443affe2a15c0ed","type":"inject","z":"da8a6ef0b3c9a5c8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"LogDateTime\":\"2023-04-01T10:01:02.003Z\",\"DayCounter\":1,\"ProductionNumber\":505050},{\"LogDateTime\":\"2023-04-01T10:02:02.003Z\",\"DayCounter\":2,\"ProductionNumber\":505050},{\"LogDateTime\":\"2023-04-01T10:03:02.003Z\",\"DayCounter\":3,\"ProductionNumber\":505050}]","payloadType":"json","x":150,"y":2780,"wires":[["a4041c0857047ac8"]]},{"id":"a4041c0857047ac8","type":"change","z":"da8a6ef0b3c9a5c8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t    $series := $distinct( $$.payload.ProductionNumber)[];\t    [\t        {\t           \"series\": $series,\t           \"data\": $series[]@$k.[\t                [\t                    $$.payload[ProductionNumber = $k].{\"x\": $.LogDateTime, \"y\":$.DayCounter}\t                ]\t            ],\t            \"labels\": $series\t        }\t    ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":2740,"wires":[["a91123f4e56d6d55","c8b3ed50979862f5"]]},{"id":"406134354bbc2ba3","type":"inject","z":"da8a6ef0b3c9a5c8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"LogDateTime\":\"2023-04-01T10:01:02.003Z\",\"DayCounter\":1,\"ProductionNumber\":505050},{\"LogDateTime\":\"2023-04-01T10:02:02.003Z\",\"DayCounter\":2,\"ProductionNumber\":505050},{\"LogDateTime\":\"2023-04-01T10:03:02.003Z\",\"DayCounter\":3,\"ProductionNumber\":505050},{\"LogDateTime\":\"2023-04-01T10:10:02.003Z\",\"DayCounter\":4,\"ProductionNumber\":505066},{\"LogDateTime\":\"2023-04-01T10:11:02.003Z\",\"DayCounter\":5,\"ProductionNumber\":505066}]","payloadType":"json","x":150,"y":2880,"wires":[["a4041c0857047ac8"]]},{"id":"a91123f4e56d6d55","type":"debug","z":"da8a6ef0b3c9a5c8","name":"debug 223","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload[0]","targetType":"msg","statusVal":"","statusType":"auto","x":550,"y":2780,"wires":[]},{"id":"c8b3ed50979862f5","type":"ui_chart","z":"da8a6ef0b3c9a5c8","name":"","group":"e1e6d940e1ac6f1a","order":1,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":610,"y":2860,"wires":[[]]},{"id":"e1e6d940e1ac6f1a","type":"ui_group","name":"Group 1","tab":"33d398e424acd7a2","order":1,"disp":true,"width":"24","collapse":false,"className":""},{"id":"33d398e424acd7a2","type":"ui_tab","name":"Tab 5","icon":"dashboard","order":5,"disabled":false,"hidden":false}]

Here's the fix, edited example above.

(
    $series := $distinct( $$.payload.ProductionNumber)[];
    [
        {
           "series": $series,
           "data": $series[]@$k.[
                [
                    $$.payload[ProductionNumber = $k].{"x": $.LogDateTime, "y":$.DayCounter}
                ]
            ],
            "labels": $series
        }
    ]
)
17

Hmm. Using the various versions above and data for a single machine I still get either a blank chart or "series not an array".

Edit: Apologies if this is irrelevant for the OP. The thread title does explicitly say "multiple series"

Here is a flow with my data and the most recent version from above. Also one I tweeked by adding another [] copied from your earlier version. I have no idea at all why it makes a difference!

Untitled 1
Untitled 1800Ă—258 8.22 KB

18

Try it again just edited it.

chrome_screenshot_1673028251326
chrome_screenshot_16730282513262048Ă—840 169 KB 

(
    $series := $distinct( $$.payload.ProductionNumber)[];
    [
        {
           "series": $series,
           "data": $series[]@$k.[
                [
                    $$.payload[ProductionNumber = $k].{"x": $.LogDateTime, "y":$.DayCounter}
                ]
            ],
            "labels": $series
        }
    ]
)
19

:+1: Thanks!

I edited my post to conceal my tweeked version for fear of confusion.

20

No I work in the energy/power sector

Yea this is basically the graph I'm looking for, but I might not have been clear that the production numbers are for parts not machines, all the data is from a single machine that makes a single part but that part can have many different variations/specifications depending on the order, each variant has their own production number.
For example it can start off and make 50 units of production number 505050, then after changing machine parameters/materials make 100 of production number 505066(or whatever that orders number might be).

So chart at the end of the day will be a line going from zero to whatever the total amount of parts from that machine for the day is, with gaps in the line separating each different part variant (production number).

No no please do not apologise, it is very relevant, there will almost certainly be days where only one production number used, so I'd like to thank you both for catching that.

Unfortunately I won't be able to test the code for myself until after the weekend.

1 Like