Empty values not shown

[themanfrommoon]

Hello,

I’m new to Grafana and I try to build my first dashboards and panels.

I have an issue with “empty values”. I have some channels that do not continuously generate data. For example, the power consumption of the robotic lawn mower, power consumption of the pumps or the garden water for watering the garden. These devices generate data only in summer, but not in winter.
However, in the display I would still have shown the months, of course, even if the corresponding devices did not generate any values in those months. Do you understand what I mean?
Let me explain. In these two panels, the time breakdown is identical, so you can visually compare them directly with each other:

But if I put the following two panels one below the other, the temporal distribution is totally different and you can no longer compare anything visually, because the pumps did not run in every month.
I would like to have the same months in the pump panel, but then with the values “0”.

[themanfrommoon]

My data source is a JSON, so I guess cannot do any SQL queries or similar:

I edited some screenshots to show how I imagine the panel to look like:

Maybe one or the other has faced the same challenges and found solutions to them?

I am grateful for any tips how to solve this issue!

Thanks a lot and best regards,
Chris

[themanfrommoon]

Sorry, I messed up with the sub category.
I used “Bar Chart (BETA)” Could any admin please move this thread to the “Bar Chart (BETA)” sub category?
Thanks a lot and sorry for the circumstance.
After moving, this post can be deleted.
Thank you.

[yosiasz]

You want to fill the gaps? What is your data source? Can you provide full data payload of you are visualizing?

You could do use jsonata maybe?

[themanfrommoon]

I do not want to fill zeros in the database.
I want Grafana to display the month, even when there are no data available.
This means Grafana should not delete empty values from the x-axis.

Excel does it like intended:

I hope I have it right what you requested, a full data payload?!

response:Array[2]
0:Object
target:"Pumpe1 Leistung"
datapoints:Array[9]
0:Array[0.397,1604185200000]
1:Array[0,1619820000000]
2:Array[2215.912,1651356000000]
3:Array[4031.143,1654034400000]
4:Array[3854.773,1656626400000]
5:Array[8357.636,1659304800000]
6:Array[753.343,1661983200000]
7:Array[5106.549,1664575200000]
8:Array[719.781,1667257200000]
1:Object
target:"Pumpe2 Leistung"
datapoints:Array[9]
0:Array[0.253,1604185200000]
1:Array[0,1619820000000]
2:Array[1152.763,1651356000000]
3:Array[4283.803,1654034400000]
4:Array[2415.326,1656626400000]
5:Array[6345.045,1659304800000]
6:Array[0,1661983200000]
7:Array[4605.837,1664575200000]
8:Array[701.888,1667257200000]

Thank you and best regards,
Chris

[yosiasz]

what is your datasource? you mention database

[themanfrommoon]

The datasource is an adapter between Grafana and Volkszähler called “gravo”
GitHub - andig/gravo: Grafana for Volkszaehler

Volkszähler itself uses a MySQL Database, but this is not performant for big data amounts. (I have over 8GB data within 2 years of operation. But the goal is to have 10-20 years of data stored.

Volkszähler has a kind of automated aggregation.
This aggregation is used by gravo

Grafana and gravo are “connected” via JSON:

@themanfrommoon

My data source is a JSON, so I guess cannot do any SQL queries or similar:

gravo - Grafana for Volkszaehler - is an adapter for using Grafana with Volkszaehler.

While it is possible to run Grafana against the Volkszaehler database directly using the Grafana MySQL datasource, gravo supports additional features:

• metrics discovery: all public channels are discoverable via the Grafana UI, private channels can also be used by adding them via their UUID
• custom channel name: you can change the channels name for Grafana
• performance: using Volkszaehler data aggregation gravo can achieve sub-second query times even when retrieving multiple years of data similar to the native Volkszaehler UI

[yosiasz]

@themanfrommoon

Grafana and gravo are “connected” via JSON:

then please post your JSON data. maybe this can be done via jsonata

[themanfrommoon]

I’m not sure how to get the JSON data. Any hint?
I tried it with following command in a webbrowser:

http://192.168.178.10/middleware/data/a8a2a080-e0df-11ea-be9e-214ff7ee2411.json?from=01-08-2020&to=31-11-2022&group=month

and I got the result:

{"version":"0.3","data":{"tuples":[[1604755149601,0,1],[1620146286986,0,1],[1654034340110,0.235,13],[1656626399423,5.599,30],[1659304798593,5.181,31],[1661983199867,11.233,31],[1664575155793,1.046,30],[1667257140429,6.854,31],[1668339601544,2.394,13]],"uuid":"a8a2a080-e0df-11ea-be9e-214ff7ee2411","from":1598257260531,"to":1668339601544,"min":[1620146286986,0],"max":[1661983199867,11.23337597015274],"average":1.286,"consumption":25039.533,"rows":10}}

I think the data are compareable with the JSON data in my previous post:

@themanfrommoon

response:Array[2]
0:Object
target:"Pumpe1 Leistung"
datapoints:Array[9]
0:Array[0.397,1604185200000]
1:Array[0,1619820000000]
2:Array[2215.912,1651356000000]
3:Array[4031.143,1654034400000]
4:Array[3854.773,1656626400000]
5:Array[8357.636,1659304800000]
6:Array[753.343,1661983200000]
7:Array[5106.549,1664575200000]
8:Array[719.781,1667257200000]
1:Object
target:"Pumpe2 Leistung"
datapoints:Array[9]
0:Array[0.253,1604185200000]
1:Array[0,1619820000000]
2:Array[1152.763,1651356000000]
3:Array[4283.803,1654034400000]
4:Array[2415.326,1656626400000]
5:Array[6345.045,1659304800000]
6:Array[0,1661983200000]
7:Array[4605.837,1664575200000]
8:Array[701.888,1667257200000]

[yosiasz]

yes they are “comparable” but the latter is not json

[themanfrommoon]

I have no idea how to get the JSON data you request.
How can I get them?

[yosiasz]

I think this is good (always post with 3 tick marks ` before and after data blob)
but where is target:“Pumpe1 Leistung” in this?

{
	"version": "0.3",
	"data": {
		"tuples": [
			[
				1604755149601,
				0,
				1
			],
			[
				1620146286986,
				0,
				1
			],
			[
				1654034340110,
				0.235,
				13
			],
			[
				1656626399423,
				5.599,
				30
			],
			[
				1659304798593,
				5.181,
				31
			],
			[
				1661983199867,
				11.233,
				31
			],
			[
				1664575155793,
				1.046,
				30
			],
			[
				1667257140429,
				6.854,
				31
			],
			[
				1668339601544,
				2.394,
				13
			]
		],
		"uuid": "a8a2a080-e0df-11ea-be9e-214ff7ee2411",
		"from": 1598257260531,
		"to": 1668339601544,
		"min": [
			1620146286986,
			0
		],
		"max": [
			1661983199867,
			11.23337597015274
		],
		"average": 1.286,
		"consumption": 25039.533,
		"rows": 10
	}
}```

[themanfrommoon]

It is the answer from the web request via a http request I typed in the web browser. There is no target. And it is the answer in the JSON format in the browser. This has nothing to do with Grafana.

In Grafana I have no idea how to get the JSON answer from the data source I defined in Grafana. Is there any possibility to see the JSON answer in Grafana? (then I can copy&paste this here).

[themanfrommoon]

I thought about it and came to a very simple idea.
Is it possible to add a constant time x-axis (maybe as a second data source)?

If yes I think the valies will be distributed as intended.

[grant2]

@themanfrommoon

I think you already know this, but you already have timestamps from your HTTP call. The times in are in epoch time (e.g. 1604755149601 = Saturday, November 7, 2020 1:19:09.601 PM in GMT)

{"version":"0.3","data":{"tuples":[[1604755149601,0,1],[1620146286986,0,1],[1654034340110,0.235,13],[1656626399423,5.599,30],[1659304798593,5.181,31],[1661983199867,11.233,31],[1664575155793,1.046,30],[1667257140429,6.854,31],[1668339601544,2.394,13]],"uuid":"a8a2a080-e0df-11ea-be9e-214ff7ee2411","from":1598257260531,"to":1668339601544,"min":[1620146286986,0],"max":[1661983199867,11.23337597015274],"average":1.286,"consumption":25039.533,"rows":10}}

But moving back to your original question, have you considered stacked bars, like this:

or using the relative time as shown (in my example, the price of Nickel prior to Apr 10 or so does not exist, yet the graph still displays back to January):

[themanfrommoon]

I tried all of that, but in the end with no satisfied result as intended.
relative time seems to work somehow in the time based series graph:

but not in a bar chart:

stacked all up make not really sense for water consumption and power consumption. The small values are much too small:

and side by side is also too small:

There is no good solution up til now.
It should look like this:
https://community.grafana.com/uploads/short-url/vBrrJQemaHwe99OM4L1dpY1Lp1d.png

The same way like excel does is needed (a constant time interval on the x-axis):
https://community.grafana.com/uploads/short-url/sG2FEDI7VklxnLimKvA6yDqOqwB.png

[yosiasz]

check this out. You might be able to do it with jsonata a bit tedious but you have more control over filling in the missing months.

https://try.jsonata.org/1TLl68YrO

[yosiasz]

this jsonata does the job

(
    $data := data.tuples;
    $years := [2020..2022];
    $months := [1..12];

    $dynamic_year_month := $map($years, function($y) {
        $map($months, function($m) {
            $y & '-' & $m 
        })
    });    

    $source_year_month := function($t) {
        $map($t,function($v) 
        {
           $fromMillis($v[0], '[Y]-[M]')
        } 
    )};
    
    /* check to see if year_month exists in source data*/

    $gap_data := function($ym) {
        $map($ym,function($v) 
        {
            $not($v in $sym) ? 
            { 
                'date': $v,
                'temp': 0,
                'psi': 0
            }
        } 
    )};

    $source_data := function($ym) {
        $map($ym,function($v) 
        {
            { 
                'date': $fromMillis($v[0], '[Y]-[M]'),
                'temp': $v[1],
                'psi': $v[2]
            }
        } 
    )};

    $sym := $source_year_month($data);

    $sd := $source_data($data);
    $gd := $gap_data($dynamic_year_month.*);
    
    $append($sd, $gd)
)

[themanfrommoon]

Okay, and how do I use it?
Where should I add this code?

[yosiasz]

How can I calculate and add a column of percentage?

UQL + JSONATA is what you needed I think. For example, { "data": [ { "String": "A", "Number": 80 }, { "String": "B", "Number": 120 } ] } parse-json | jsonata "$map(data,function($v){{ 'S': $v.String, 'N': $v.Number, 'P': $v.Number/$sum(data[*].Number)}})" [image]

read this

Programming constructs · JSONata