Empty values not shown

@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):

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

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

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)
)
1 Like

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

read this

Sorry, I’m not able to follow you.
I have no clue how to combine my database and Grafana with JSONata.
JSONata documentaion says: “JSONata is a lightweight query and transformation language for JSON data.”
Okay, JSONata is a language (like C++ or similar) okay, understand.
But how can I use this language?
Where can I use it?
Where in Grafana can I add your code?
Do I need to install a plugin in Grafana?
Is JSONata the same as the JSON Api in Grafana? So do I need to install the JSON Api in Grafana to use JSONata?
I can’t find any information about this?!

Is your data source that you have connected or planning to connect to grafana a database or a rest api endpoint that returns json?

All I know about this is described in my post #7

1 Like

Which of these plugins are you using?

You can see it in my #2 post:
https://community.grafana.com/t/empty-values-not-shown/76173/2?u=themanfrommoon

It is the JSON plugin

1 Like

I might have a “solution” for this problem and by “solution” I mean mostly “hack”. I’m not even sure this will work with your data source but I don’t see why it wouldn’t. Anyway here is how I’ve solved it:


I’ve defined another query that always returns 0 (“filler_query”), this will add a value for every time interval on timeline and therefore create another “group” in barchart. Then I’ve set label for this value to be " " (space) so it won’t be visible in the legend and also added override for this field setting it to be transparent color.

Had to use infinity plugin for this

This could be a working idea, but I was not able to get it working.
I have no clue how to add a query. I think it is not possible with JSON?! There are no similar options:

I tried to install the infinity plugin, but it failed:


I don’t know why?!

Hm… you did add a query. There are two: “A” and “B”. Though second one should just return a constant - 0. Can you make it do that?

check your grafana log. or try another way to install it → zip file.

I tried exactly this (1st querry has the data, second query to have constant zeroes), but I have no idea how to make it return a constant -0 ?!

logger=context userId=1 orgId=1 uname=chris t=2022-11-23T18:43:13.380311563+01:00 level=error msg=“Failed to install plugin” error=“failed to extract plugin archive: could not create "/var/lib/grafana/plugins/yesoreyeram-infinity-datasource", permission denied, make sure you have write access to plugin dir” remote_addr=192.168.178.53 traceID=

Okay, now I was able to install the infinity plugin :slight_smile:

1 Like

here is the json file for the dashboard that I think is working for me

{
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": {
          "type": "grafana",
          "uid": "-- Grafana --"
        },
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "target": {
          "limit": 100,
          "matchAny": false,
          "tags": [],
          "type": "dashboard"
        },
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "fiscalYearStartMonth": 0,
  "graphTooltip": 0,
  "id": 19,
  "links": [],
  "liveNow": false,
  "panels": [
    {
      "datasource": {
        "type": "yesoreyeram-infinity-datasource",
        "uid": "c6l_dV74k"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "fillOpacity": 80,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "lineWidth": 1,
            "scaleDistribution": {
              "type": "linear"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 13,
        "w": 19,
        "x": 0,
        "y": 0
      },
      "id": 3,
      "options": {
        "barRadius": 0,
        "barWidth": 0.97,
        "groupWidth": 0.7,
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "orientation": "auto",
        "showValue": "auto",
        "stacking": "none",
        "tooltip": {
          "mode": "single",
          "sort": "none"
        },
        "xField": "date",
        "xTickLabelRotation": 0,
        "xTickLabelSpacing": 0
      },
      "targets": [
        {
          "columns": [],
          "data": "[\n  {\n    \"date\": true,\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2022-12\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2022-4\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2022-3\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2022-2\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2022-1\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": true,\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-12\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-11\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-10\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-9\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-8\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-7\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-6\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-4\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-3\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-2\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2021-1\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": true,\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-12\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-10\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-9\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-8\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-7\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-6\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-5\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-4\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-3\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-2\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2020-1\",\n    \"temp\": 0,\n    \"psi\": 0\n  },\n  {\n    \"date\": \"2022-11\",\n    \"temp\": 2.394,\n    \"psi\": 13\n  },\n  {\n    \"date\": \"2022-10\",\n    \"temp\": 6.854,\n    \"psi\": 31\n  },\n  {\n    \"date\": \"2022-9\",\n    \"temp\": 1.046,\n    \"psi\": 30\n  },\n  {\n    \"date\": \"2022-8\",\n    \"temp\": 11.233,\n    \"psi\": 31\n  },\n  {\n    \"date\": \"2022-7\",\n    \"temp\": 5.181,\n    \"psi\": 31\n  },\n  {\n    \"date\": \"2022-6\",\n    \"temp\": 5.599,\n    \"psi\": 30\n  },\n  {\n    \"date\": \"2022-5\",\n    \"temp\": 0.235,\n    \"psi\": 13\n  },\n  {\n    \"date\": \"2021-5\",\n    \"temp\": 0,\n    \"psi\": 1\n  },\n  {\n    \"date\": \"2020-11\",\n    \"temp\": 0,\n    \"psi\": 1\n  }\n]",
          "datasource": {
            "type": "yesoreyeram-infinity-datasource",
            "uid": "c6l_dV74k"
          },
          "filters": [],
          "format": "table",
          "global_query_id": "",
          "hide": false,
          "refId": "A",
          "root_selector": "",
          "source": "inline",
          "type": "json",
          "uql": "parse-json\r\n| jsonata \"($months := [1,2,3,4,5,6,7,8,9,10,11,12]; $map(data.tuples,function($v){{ 'reading': $v[1], 'date': $fromMillis($v[0], '[Y]/[M01]/[D01]'), 'year-month': $fromMillis($v[0], '[Y]-[M]'), 'year': $fromMillis($v[0], '[Y]'), 'month_exists': $number($fromMillis($v[0], '[M]')) in $months }}))\"",
          "url": "https://jsonplaceholder.typicode.com/users",
          "url_options": {
            "data": "",
            "method": "GET"
          }
        }
      ],
      "title": "Missing Months Jsonata",
      "type": "barchart"
    },
    {
      "datasource": {
        "type": "yesoreyeram-infinity-datasource",
        "uid": "c6l_dV74k"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "fillOpacity": 80,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "lineWidth": 1,
            "scaleDistribution": {
              "type": "linear"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 12,
        "w": 19,
        "x": 0,
        "y": 13
      },
      "id": 2,
      "options": {
        "barRadius": 0,
        "barWidth": 0.97,
        "groupWidth": 0.7,
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "orientation": "auto",
        "showValue": "auto",
        "stacking": "none",
        "tooltip": {
          "mode": "single",
          "sort": "none"
        },
        "xField": "year-month",
        "xTickLabelRotation": 0,
        "xTickLabelSpacing": 0
      },
      "targets": [
        {
          "columns": [],
          "data": "{\n  \"data\": {\n    \"average\": 1.286,\n    \"consumption\": 25039.533,\n    \"from\": 1598257260531,\n    \"max\": [\n      1661983199867,\n      11.233375970153\n    ],\n    \"min\": [\n      1620146286986,\n      0\n    ],\n    \"rows\": 10,\n    \"to\": 1668339601544,\n    \"tuples\": [\n      [\n        1604755149601,\n        0,\n        1\n      ],\n      [\n        1620146286986,\n        0,\n        1\n      ],\n      [\n        1654034340110,\n        0.235,\n        13\n      ],\n      [\n        1656626399423,\n        5.599,\n        30\n      ],\n      [\n        1659304798593,\n        5.181,\n        31\n      ],\n      [\n        1661983199867,\n        11.233,\n        31\n      ],\n      [\n        1664575155793,\n        1.046,\n        30\n      ],\n      [\n        1667257140429,\n        6.854,\n        31\n      ],\n      [\n        1668339601544,\n        2.394,\n        13\n      ]\n    ],\n    \"uuid\": \"a8a2a080-e0df-11ea-be9e-214ff7ee2411\"\n  },\n  \"version\": \"0.3\"\n}",
          "datasource": {
            "type": "yesoreyeram-infinity-datasource",
            "uid": "c6l_dV74k"
          },
          "filters": [],
          "format": "table",
          "global_query_id": "",
          "refId": "A",
          "root_selector": "",
          "source": "inline",
          "type": "uql",
          "uql": "parse-json\r\n| jsonata \"($months := [1,2,3,4,5,6,7,8,9,10,11,12]; $map(data.tuples,function($v){{ 'reading': $v[1], 'date': $fromMillis($v[0], '[Y]/[M01]/[D01]'), 'year-month': $fromMillis($v[0], '[Y]-[M]'), 'year': $fromMillis($v[0], '[Y]'), 'month_exists': $number($fromMillis($v[0], '[M]')) in $months }}))\"",
          "url": "https://jsonplaceholder.typicode.com/users",
          "url_options": {
            "data": "",
            "method": "GET"
          }
        }
      ],
      "title": "Missing Months Jsonata",
      "type": "barchart"
    },
    {
      "datasource": {
        "type": "yesoreyeram-infinity-datasource",
        "uid": "c6l_dV74k"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "fillOpacity": 80,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "lineWidth": 1,
            "scaleDistribution": {
              "type": "linear"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 12,
        "w": 19,
        "x": 0,
        "y": 25
      },
      "id": 4,
      "options": {
        "barRadius": 0,
        "barWidth": 0.97,
        "groupWidth": 0.7,
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "orientation": "auto",
        "showValue": "auto",
        "stacking": "none",
        "tooltip": {
          "mode": "single",
          "sort": "none"
        },
        "xField": "date",
        "xTickLabelRotation": 0,
        "xTickLabelSpacing": 0
      },
      "targets": [
        {
          "columns": [],
          "data": "{\n  \"data\": {\n    \"average\": 1.286,\n    \"consumption\": 25039.533,\n    \"from\": 1598257260531,\n    \"max\": [\n      1661983199867,\n      11.233375970153\n    ],\n    \"min\": [\n      1620146286986,\n      0\n    ],\n    \"rows\": 10,\n    \"to\": 1668339601544,\n    \"tuples\": [\n      [\n        1604755149601,\n        0,\n        1\n      ],\n      [\n        1620146286986,\n        0,\n        1\n      ],\n      [\n        1654034340110,\n        0.235,\n        13\n      ],\n      [\n        1656626399423,\n        5.599,\n        30\n      ],\n      [\n        1659304798593,\n        5.181,\n        31\n      ],\n      [\n        1661983199867,\n        11.233,\n        31\n      ],\n      [\n        1664575155793,\n        1.046,\n        30\n      ],\n      [\n        1667257140429,\n        6.854,\n        31\n      ],\n      [\n        1668339601544,\n        2.394,\n        13\n      ]\n    ],\n    \"uuid\": \"a8a2a080-e0df-11ea-be9e-214ff7ee2411\"\n  },\n  \"version\": \"0.3\"\n}",
          "datasource": {
            "type": "yesoreyeram-infinity-datasource",
            "uid": "c6l_dV74k"
          },
          "filters": [],
          "format": "timeseries",
          "global_query_id": "",
          "refId": "A",
          "root_selector": "",
          "source": "inline",
          "type": "uql",
          "uql": "parse-json\r\n| jsonata \"( $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) )\"",
          "url": "https://jsonplaceholder.typicode.com/users",
          "url_options": {
            "data": "",
            "method": "GET"
          }
        }
      ],
      "title": "Missing Months Jsonata",
      "transformations": [
        {
          "disabled": true,
          "id": "convertFieldType",
          "options": {
            "conversions": [
              {
                "dateFormat": "YYYY-MM",
                "destinationType": "time",
                "targetField": "date"
              }
            ],
            "fields": {}
          }
        }
      ],
      "type": "barchart"
    }
  ],
  "refresh": false,
  "schemaVersion": 37,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": []
  },
  "time": {
    "from": "now-24h",
    "to": "now"
  },
  "timepicker": {},
  "timezone": "",
  "title": "Missing Months",
  "uid": "uUOdyhO4z",
  "version": 7,
  "weekStart": ""
}

Import it and see what happens. You might have to tweak some things if it does not work. Try to work out the issues on your own.