InfluxDB stacked bar flux query tasmota watt

gistfile1.txt

import "strings"
from(bucket: "homeassistant")
  // Range is set MANUALLY! Currently it's daily for a month, but you could change it to -1y here!
  |> range(start: -30d)
  // That's the MQTT measurement my devices use, YMMV
  |> filter(fn: (r) => r["_measurement"] == "tasmota")
  // That's the Tasmota field "Total". Should be the same for anyone.
  |> filter(fn: (r) => r["_field"] == "Total")
  // Make sensor names readable. Mine get stored as "tele/tasmota_office" for example, and I only want to get "office". Change strings to your setup.
  |> map(fn: (r) =>  ({ r with topic: strings.replaceAll(v: strings.replaceAll(v: r.topic, t:"/SENSOR", u:""), t:"tele/tasmota_", u:"") }))
  // Convert kwH to wH (for smaller resolution)
  |> map(fn: (r) =>  ({ r with _value: r._value * 1000.0 }))
  // The actual resolution of items. Since I use monthly, I plot per day. If you change it to yearly you may want to use "30d" here or "1mo"!
  |> aggregateWindow(every: 1d, fn: max)
  // That's the stacking part.
  |> difference()
  // Display name
  |> yield(name: "Total Usage")
  
  
  // Other notes:
  // * In grafana you want to set "Query options > Relative time" to the same as above (30d or 1y); see JSON "timeFrom" (and resulting "maxDataPoints") key!
  
  // My total panel JSON:
  {
  "id": 10,
  "gridPos": {
    "h": 11,
    "w": 24,
    "x": 0,
    "y": 49
  },
  "type": "timeseries",
  "title": "Stromverbrauch (Monat)",
  "pluginVersion": "8.3.1",
  "timeFrom": "30d",
  "maxDataPoints": 30,
  "description": "",
  "fieldConfig": {
    "defaults": {
      "custom": {
        "drawStyle": "bars",
        "lineInterpolation": "linear",
        "barAlignment": 0,
        "lineWidth": 1,
        "fillOpacity": 80,
        "gradientMode": "none",
        "spanNulls": true,
        "showPoints": "never",
        "pointSize": 5,
        "stacking": {
          "mode": "normal",
          "group": "A"
        },
        "axisPlacement": "auto",
        "axisLabel": "",
        "scaleDistribution": {
          "type": "linear"
        },
        "hideFrom": {
          "tooltip": false,
          "viz": false,
          "legend": false
        },
        "thresholdsStyle": {
          "mode": "off"
        }
      },
      "color": {
        "mode": "palette-classic"
      },
      "thresholds": {
        "mode": "absolute",
        "steps": [
          {
            "color": "green",
            "value": null
          }
        ]
      },
      "mappings": [],
      "displayName": "${__field.labels.topic}",
      "min": 0,
      "unit": "watt"
    },
    "overrides": []
  },
  "options": {
    "tooltip": {
      "mode": "multi"
    },
    "legend": {
      "displayMode": "table",
      "placement": "bottom",
      "calcs": [
        "first",
        "last"
      ]
    }
  },
  "targets": [
    {
      "datasource": {
        "type": "influxdb",
        "uid": "INSERT_YOUR_TOKEN_HERE"
      },
      "groupBy": [
        {
          "params": [
            "$__interval"
          ],
          "type": "time"
        },
        {
          "params": [
            "null"
          ],
          "type": "fill"
        }
      ],
      "orderByTime": "ASC",
      "policy": "default",
      "query": "import \"strings\"\n\nfrom(bucket: \"homeassistant\")\n  |> range(start: -30d)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"tasmota\")\n  |> filter(fn: (r) => r[\"_field\"] == \"Total\")\n  |> map(fn: (r) =>  ({ r with topic: strings.replaceAll(v: strings.replaceAll(v: r.topic, t:\"/SENSOR\", u:\"\"), t:\"tele/tasmota_\", u:\"\") }))\n  |> map(fn: (r) =>  ({ r with _value: r._value * 1000.0 }))\n  |> aggregateWindow(every: 1d, fn: max)\n  |> difference()\n  |> yield(name: \"Total Usage\")\n",
      "refId": "A",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "value"
            ],
            "type": "field"
          },
          {
            "params": [],
            "type": "mean"
          }
        ]
      ],
      "tags": []
    }
  ],
  "datasource": null
}