Grafana 8 Time Series, stacking negative values bar not showing

Grafana Time Series Panel
1

I converted the old graph visualization to the new time series.

somehow the two negative bars are not playing nice. if I hide one of them the visualization works but with both of them together, one is not showing the bar.

image
The tooltip is showing the missing bar.

The visualization is showing two stacks, A (normal and low use) and C (normal and low delivery)

Please help!

image
image843×459 28.6 KB

Grafana 8.0.2, part of Home Assistant Add-on Link to add-on page

Json below

{
	"annotations": {
		"list": [
			{
				"builtIn": 1,
				"datasource": "-- Grafana --",
				"enable": true,
				"hide": true,
				"iconColor": "rgba(0, 211, 255, 1)",
				"name": "Annotations & Alerts",
				"type": "dashboard"
			}
		]
	},
	"editable": true,
	"gnetId": null,
	"graphTooltip": 0,
	"id": 5,
	"links": [],
	"panels": [
		{
			"aliasColors": {
				"Low Tariff": "#614d93",
				"Low Tariff Use": "semi-dark-purple",
				"Low Tariff delivery": "light-purple",
				"Nett Electricity": "dark-orange",
				"Normal Tariff": "#f4d598",
				"Normal Tariff Use": "light-orange",
				"Normal Tariff delivery": "super-light-orange",
				"kWh.difference": "#0a50a1"
			},
			"bars": true,
			"dashLength": 10,
			"dashes": false,
			"datasource": "InfluxDB",
			"description": "",
			"fill": 1,
			"fillGradient": 0,
			"gridPos": {
				"h": 12,
				"w": 9,
				"x": 0,
				"y": 0
			},
			"hiddenSeries": false,
			"hideTimeOverride": true,
			"id": 4,
			"interval": null,
			"legend": {
				"avg": false,
				"current": false,
				"max": false,
				"min": false,
				"show": true,
				"total": true,
				"values": true
			},
			"lines": false,
			"linewidth": 1,
			"links": [],
			"nullPointMode": "null",
			"options": {
				"alertThreshold": false
			},
			"percentage": false,
			"pluginVersion": "8.0.2",
			"pointradius": 5,
			"points": false,
			"renderer": "flot",
			"seriesOverrides": [
				{
					"$$hashKey": "object:1183",
					"alias": "Low Tariff delivery",
					"stack": "C"
				},
				{
					"$$hashKey": "object:1203",
					"alias": "Normal Tariff delivery",
					"stack": "C"
				},
				{
					"$$hashKey": "object:286",
					"alias": "Nett Electricity",
					"bars": false,
					"fillGradient": 0,
					"points": true,
					"stack": false
				}
			],
			"spaceLength": 10,
			"stack": true,
			"steppedLine": false,
			"targets": [
				{
					"alias": "Normal Tariff Use",
					"groupBy": [
						{
							"params": [
								"1d"
							],
							"type": "time"
						},
						{
							"params": [
								"previous"
							],
							"type": "fill"
						}
					],
					"hide": false,
					"measurement": "kWh",
					"orderByTime": "ASC",
					"policy": "default",
					"query": "SELECT last(\"value\") - first(\"value\") FROM \"kWh\" WHERE (\"entity_id\" = 'energy_consumption_tarif_2') AND $timeFilter GROUP BY time(1w)",
					"rawQuery": true,
					"refId": "B",
					"resultFormat": "time_series",
					"select": [
						[
							{
								"params": [
									"value"
								],
								"type": "field"
							},
							{
								"params": [],
								"type": "mean"
							},
							{
								"params": [],
								"type": "difference"
							}
						]
					],
					"tags": [
						{
							"key": "entity_id",
							"operator": "=",
							"value": "power_consumption_normal"
						}
					]
				},
				{
					"alias": "Low Tariff Use",
					"groupBy": [
						{
							"params": [
								"1d"
							],
							"type": "time"
						},
						{
							"params": [
								"previous"
							],
							"type": "fill"
						}
					],
					"hide": false,
					"measurement": "kWh",
					"orderByTime": "ASC",
					"policy": "default",
					"query": "SELECT last(\"value\") - first(\"value\") FROM \"kWh\" WHERE (\"entity_id\" = 'energy_consumption_tarif_1') AND $timeFilter GROUP BY time(1w)\n",
					"rawQuery": true,
					"refId": "A",
					"resultFormat": "time_series",
					"select": [
						[
							{
								"params": [
									"value"
								],
								"type": "field"
							},
							{
								"params": [],
								"type": "mean"
							},
							{
								"params": [],
								"type": "difference"
							}
						]
					],
					"tags": [
						{
							"key": "entity_id",
							"operator": "=",
							"value": "power_consumption_low"
						}
					]
				},
				{
					"alias": "Low Tariff delivery",
					"groupBy": [
						{
							"params": [
								"$__interval"
							],
							"type": "time"
						},
						{
							"params": [
								"null"
							],
							"type": "fill"
						}
					],
					"hide": false,
					"measurement": "kWh",
					"orderByTime": "ASC",
					"policy": "default",
					"query": "SELECT (last(\"value\") - first(\"value\"))*-1 FROM \"kWh\" WHERE (\"entity_id\" = 'energy_production_tarif_1') AND $timeFilter GROUP BY time(1w)",
					"rawQuery": true,
					"refId": "C",
					"resultFormat": "time_series",
					"select": [
						[
							{
								"params": [
									"value"
								],
								"type": "field"
							},
							{
								"params": [],
								"type": "mean"
							},
							{
								"params": [],
								"type": "difference"
							}
						]
					],
					"tags": [
						{
							"key": "entity_id",
							"operator": "=",
							"value": "energy_production_tarif_1"
						}
					]
				},
				{
					"alias": "Normal Tariff delivery",
					"groupBy": [
						{
							"params": [
								"$__interval"
							],
							"type": "time"
						},
						{
							"params": [
								"null"
							],
							"type": "fill"
						}
					],
					"hide": false,
					"measurement": "kWh",
					"orderByTime": "ASC",
					"policy": "default",
					"query": "SELECT (last(\"value\") - first(\"value\"))*-1 FROM \"kWh\" WHERE (\"entity_id\" = 'energy_production_tarif_2') AND $timeFilter GROUP BY time(1w)",
					"rawQuery": true,
					"refId": "D",
					"resultFormat": "time_series",
					"select": [
						[
							{
								"params": [
									"value"
								],
								"type": "field"
							},
							{
								"params": [],
								"type": "mean"
							},
							{
								"params": [],
								"type": "difference"
							}
						]
					],
					"tags": [
						{
							"key": "entity_id",
							"operator": "=",
							"value": "energy_production_tarif_1"
						}
					]
				},
				{
					"datasource": "__expr__",
					"expression": "$A+$B+$C+$D",
					"hide": false,
					"refId": "Nett Electricity",
					"type": "math"
				}
			],
			"thresholds": [],
			"timeFrom": "1y",
			"timeRegions": [],
			"timeShift": "1h",
			"title": "Electricity Use past year (Graph)",
			"tooltip": {
				"shared": true,
				"sort": 0,
				"value_type": "individual"
			},
			"transparent": true,
			"type": "graph",
			"xaxis": {
				"buckets": null,
				"mode": "time",
				"name": null,
				"show": true,
				"values": []
			},
			"yaxes": [
				{
					"$$hashKey": "object:79",
					"format": "kwatth",
					"label": "",
					"logBase": 1,
					"max": null,
					"min": null,
					"show": true
				},
				{
					"$$hashKey": "object:80",
					"format": "short",
					"label": null,
					"logBase": 1,
					"max": null,
					"min": null,
					"show": false
				}
			],
			"yaxis": {
				"align": false,
				"alignLevel": null
			}
		},
		{
			"datasource": "InfluxDB",
			"description": "",
			"fieldConfig": {
				"defaults": {
					"color": {
						"mode": "palette-classic"
					},
					"custom": {
						"axisLabel": "",
						"axisPlacement": "auto",
						"barAlignment": 0,
						"drawStyle": "bars",
						"fillOpacity": 100,
						"gradientMode": "none",
						"hideFrom": {
							"legend": false,
							"tooltip": false,
							"viz": false
						},
						"lineInterpolation": "linear",
						"lineWidth": 1,
						"pointSize": 5,
						"scaleDistribution": {
							"type": "linear"
						},
						"showPoints": "never",
						"spanNulls": true,
						"stacking": {
							"group": "A",
							"mode": "normal"
						},
						"thresholdsStyle": {
							"mode": "off"
						}
					},
					"mappings": [],
					"thresholds": {
						"mode": "absolute",
						"steps": [
							{
								"color": "green",
								"value": null
							},
							{
								"color": "red",
								"value": 80
							}
						]
					},
					"unit": "kwatth"
				},
				"overrides": [
					{
						"matcher": {
							"id": "byName",
							"options": "Low Tariff Use"
						},
						"properties": [
							{
								"id": "color",
								"value": {
									"fixedColor": "semi-dark-purple",
									"mode": "fixed"
								}
							}
						]
					},
					{
						"matcher": {
							"id": "byName",
							"options": "Low Tariff delivery"
						},
						"properties": [
							{
								"id": "color",
								"value": {
									"fixedColor": "light-purple",
									"mode": "fixed"
								}
							},
							{
								"id": "custom.stacking",
								"value": {
									"group": "C",
									"mode": "normal"
								}
							}
						]
					},
					{
						"matcher": {
							"id": "byName",
							"options": "Normal Tariff Use"
						},
						"properties": [
							{
								"id": "color",
								"value": {
									"fixedColor": "light-orange",
									"mode": "fixed"
								}
							}
						]
					},
					{
						"matcher": {
							"id": "byName",
							"options": "Normal Tariff delivery"
						},
						"properties": [
							{
								"id": "color",
								"value": {
									"fixedColor": "super-light-orange",
									"mode": "fixed"
								}
							},
							{
								"id": "custom.stacking",
								"value": {
									"group": "C",
									"mode": "normal"
								}
							}
						]
					},
					{
						"matcher": {
							"id": "byName",
							"options": "Nett Electricity"
						},
						"properties": [
							{
								"id": "custom.drawStyle",
								"value": "points"
							},
							{
								"id": "custom.showPoints",
								"value": "always"
							},
							{
								"id": "custom.stacking",
								"value": {
									"group": false,
									"mode": "none"
								}
							},
							{
								"id": "color",
								"value": {
									"fixedColor": "dark-orange",
									"mode": "fixed"
								}
							}
						]
					}
				]
			},
			"gridPos": {
				"h": 12,
				"w": 9,
				"x": 9,
				"y": 0
			},
			"hideTimeOverride": true,
			"id": 5,
			"interval": null,
			"links": [],
			"options": {
				"legend": {
					"calcs": [
						"sum"
					],
					"displayMode": "list",
					"placement": "bottom"
				},
				"tooltip": {
					"mode": "multi"
				}
			},
			"pluginVersion": "8.0.2",
			"targets": [
				{
					"alias": "Normal Tariff Use",
					"groupBy": [
						{
							"params": [
								"1d"
							],
							"type": "time"
						},
						{
							"params": [
								"previous"
							],
							"type": "fill"
						}
					],
					"hide": false,
					"measurement": "kWh",
					"orderByTime": "ASC",
					"policy": "default",
					"query": "SELECT last(\"value\") - first(\"value\") FROM \"kWh\" WHERE (\"entity_id\" = 'energy_consumption_tarif_2') AND $timeFilter GROUP BY time(1w)",
					"rawQuery": true,
					"refId": "B",
					"resultFormat": "time_series",
					"select": [
						[
							{
								"params": [
									"value"
								],
								"type": "field"
							},
							{
								"params": [],
								"type": "mean"
							},
							{
								"params": [],
								"type": "difference"
							}
						]
					],
					"tags": [
						{
							"key": "entity_id",
							"operator": "=",
							"value": "power_consumption_normal"
						}
					]
				},
				{
					"alias": "Low Tariff Use",
					"groupBy": [
						{
							"params": [
								"1d"
							],
							"type": "time"
						},
						{
							"params": [
								"previous"
							],
							"type": "fill"
						}
					],
					"hide": false,
					"measurement": "kWh",
					"orderByTime": "ASC",
					"policy": "default",
					"query": "SELECT last(\"value\") - first(\"value\") FROM \"kWh\" WHERE (\"entity_id\" = 'energy_consumption_tarif_1') AND $timeFilter GROUP BY time(1w)\n",
					"rawQuery": true,
					"refId": "A",
					"resultFormat": "time_series",
					"select": [
						[
							{
								"params": [
									"value"
								],
								"type": "field"
							},
							{
								"params": [],
								"type": "mean"
							},
							{
								"params": [],
								"type": "difference"
							}
						]
					],
					"tags": [
						{
							"key": "entity_id",
							"operator": "=",
							"value": "power_consumption_low"
						}
					]
				},
				{
					"alias": "Low Tariff delivery",
					"groupBy": [
						{
							"params": [
								"$__interval"
							],
							"type": "time"
						},
						{
							"params": [
								"null"
							],
							"type": "fill"
						}
					],
					"hide": false,
					"measurement": "kWh",
					"orderByTime": "ASC",
					"policy": "default",
					"query": "SELECT (last(\"value\") - first(\"value\"))*-1 FROM \"kWh\" WHERE (\"entity_id\" = 'energy_production_tarif_1') AND $timeFilter GROUP BY time(1w)",
					"rawQuery": true,
					"refId": "C",
					"resultFormat": "time_series",
					"select": [
						[
							{
								"params": [
									"value"
								],
								"type": "field"
							},
							{
								"params": [],
								"type": "mean"
							},
							{
								"params": [],
								"type": "difference"
							}
						]
					],
					"tags": [
						{
							"key": "entity_id",
							"operator": "=",
							"value": "energy_production_tarif_1"
						}
					]
				},
				{
					"alias": "Normal Tariff delivery",
					"groupBy": [
						{
							"params": [
								"$__interval"
							],
							"type": "time"
						},
						{
							"params": [
								"null"
							],
							"type": "fill"
						}
					],
					"hide": false,
					"measurement": "kWh",
					"orderByTime": "ASC",
					"policy": "default",
					"query": "SELECT (last(\"value\") - first(\"value\"))*-1 FROM \"kWh\" WHERE (\"entity_id\" = 'energy_production_tarif_2') AND $timeFilter GROUP BY time(1w)",
					"rawQuery": true,
					"refId": "D",
					"resultFormat": "time_series",
					"select": [
						[
							{
								"params": [
									"value"
								],
								"type": "field"
							},
							{
								"params": [],
								"type": "mean"
							},
							{
								"params": [],
								"type": "difference"
							}
						]
					],
					"tags": [
						{
							"key": "entity_id",
							"operator": "=",
							"value": "energy_production_tarif_1"
						}
					]
				},
				{
					"datasource": "__expr__",
					"expression": "$A+$B+$C+$D",
					"hide": false,
					"refId": "Nett Electricity",
					"type": "math"
				}
			],
			"timeFrom": "1y",
			"timeShift": "1h",
			"title": "Electricity Use past year (time series)",
			"transparent": true,
			"type": "timeseries"
		}
	],
	"schemaVersion": 30,
	"style": "dark",
	"tags": [],
	"templating": {
		"list": []
	},
	"time": {
		"from": "now-6h",
		"to": "now"
	},
	"timepicker": {},
	"timezone": "",
	"title": "Solar",
	"uid": "GDQjtCZRk",
	"version": 4
}
2

Does anyone have an idea?

would love to change over to time series because the visualization is better.

3

Have you set stacking to off? Or is it set to normal? What does stacking read in your time series panel json:

"stacking": {
              "group": "A",
              "mode": "none"
            },
4

Hi Matt,
Thanks for your reply.
In my first message the JSON of both the time series as the old graph is present.

Looking at you JSON suggestion I can only find that notation in the old graph JSON.

Looks like the time series has a different notation?

Thanks for the help!

5

yes, to clarify are you using the Graph (old) panel or the timeseries panel in Grafana 8?

6

I have the exact same problem. I am using the timeseries panel in Grafana 8 (version: 8.3.3).

I have A and B, both negative. I plot them as bars and stack them. The stacking does not work. If I change the SQL and plot -A and -B (so that both become positive), then the stacking does work.

Conclusion: Stacking of negative values does not work.

7

if you plot the queries as lines, do they stack? Or is it only when plotting as bars?

8

When I plot the queries as lines, they do not stack, either.

1 Like
9

Hi Mattabrams, I’m having the same issue, using the new timeseries panel in grafana 8.
if you stack negative values as a bar or line it doesn’t work, as points it does seem to work correctly. Any help is appreciated!

10

Why this topic is closed? Does this issue have a solution?

11

I have the same problem. I’m using Grafana Cloud.

12

This topic was automatically closed after 365 days. New replies are no longer allowed.