Highcharts columnrange charts with datetime intervals

[Flogge]

Hey folks!

I’m currently lost with highcharts configurations for a columnrange chart I want to use to visualize execution times of Schedules running on our instance.
This is a rough idea of what I’m trying to achieve (assembled in a OD Report in 2 minutes so don’t be too picky on my color scheme and such):

I am using 3 columns:

• Name of the schedule (String)
• Start time (Datetime)
• End Time (Datetime)
  When I try to get something similar in Apps it won’t work with DATETIME data types but only with INT or other numeric values.
  Datasource is as follows:

{
  "id": "foo",
  "origin": "raw",
  "config": {
    "schema": "table",
    "tableSchema": [
      {
        "name": "schedule_id",
        "type": "STRING",
        "index": 0
      },
      {
        "name": "start_time",
        "type": "DATETIME",
        "index": 1
      },
      {
        "name": "end_time",
        "type": "DATETIME",
        "index": 2
      }
    ],
    "data": [
      {
        "schedule_id": "Schedule 1",
        "start_time": "2020-01-20T01:00:00.0Z",
        "end_time": "2020-01-20T02:00:00.0Z"
      },
      {
        "schedule_id": "Schedule 2",
        "start_time": "2020-01-20T01:00:00.0Z",
        "end_time": "2020-01-20T04:00:00.0Z"
      },
      {
        "schedule_id": "Schedule 3",
        "start_time": "2020-01-20T02:00:00.0Z",
        "end_time": "2020-01-20T05:00:00.0Z"
      },
      {
        "schedule_id": "Schedule 1",
        "start_time": "2020-01-20T03:00:00.0Z",
        "end_time": "2020-01-20T06:00:00.0Z"
      }
    ]
  }
}

Element definition is as follows (adapted from https://jsfiddle.net/gh/get/library/pure/highcharts/highcharts/tree/master/samples/highcharts/demo/columnrange):

{
  "id": "elem_scheduled_executions",
  "type": "highcharts",
  "config": {
    "chart": {
      "type": "columnrange",
      "inverted": true
    },
    "grouping": true,
    "connectNulls": true,
    "title": {
      "text": "Schedule Executions"
    },
    "xAxis": {
      "title": "Schedule"
    },
    "yAxis": {
      "title": {
        "text": "Execution Times"
      },
      "type": "datetime",
      "plotOptions": {
        "columnrange": {
          "dataLabels": {
            "enabled": true,
            "format": "{y}"
          }
        }
      }
    },
    "legend": {
      "enabled": false
    },
    "series": [
      {
        "name": "Schedule",
        "columnName": "schedule_id"
      },
      {
        "name": "end",
        "columnName": "end_time"
      },
      {
        "name": "start",
        "columnName": "start_time"
      }
    ]
  },
  "source": "bar"
}

The rendered graph only shows the schedule names but otherwise stays empty. If I switch to INT for start and end, I can at least get the bars to show:

Still there are two major painpoints with this otherwise feasible workaround:

1. The Schedule times for Schedule 1 get shown in two separate “columns” instead of having only three columns (one for each schedule, two bars for Schedule 1)
2. Tooltips and data points refuse to be formatted as datetime no matter what I do.

If someone has a working example (e.g. a Gantt chart otl built in Apps), please let me know!

PS: There are no console errors and I also tried playing around with data types a bit (used STRING and different TS formats).

[Flogge]

OK. Got the Label part for both axis and data points.

{
  "id": "elem_scheduled_executions",
  "type": "highcharts",
  "config": {
    "chart": {
      "type": "columnrange",
      "inverted": true
    },
    "data": {
      "dateFormat": "MM-dd hh:mm"
    },
    "grouping": true,
    "connectNulls": true,
    "title": {
      "text": "Schedule Executions"
    },
    "xAxis": {
      "title": "Schedule"
    },
    "yAxis": {
      "title": {
        "text": "Execution Times"
      },
      "labels": {
        "format": "{value:%Y-%m-%d %H:%M}"
      },
      "type": "datetime"
    },
    "plotOptions": {
      "columnrange": {
        "dataLabels": {
          "enabled": true,
          "format": "{y:%H:%M}",
          "crop": true
        }
      }
    },
    "legend": {
      "enabled": false
    },
    "series": [
      {
        "name": "Schedule",
        "columnName": "schedule_id"
      },
      {
        "name": "end",
        "columnName": "end_time"
      },
      {
        "name": "start",
        "columnName": "start_time"
      }
    ]
  },
  "source": "bar"
}

This brings me to:

Still, the “non-grouped” values for schedule 1 issue remains.

[Flogge]

Finally, I also found the culprit for the two columns for Schedule 1: "uniqueNames": true has to be set explicitly for the xAxis, despite the Highcharts API documentation indicating that true is already the default (see xAxis.uniqueNames | Highcharts JS API Reference).

"xAxis": {
  "type": "category",
  "title": "Schedule",
  "uniqueNames": true,
  "visible": false
}

[simon.neumeyer]

Is it possible to color each point according to a certain information that the data row is carrying, e.g. a project with project start and end date should be coloured according to its status? (More general: Can any such additional information be accessed on a point level?)

[Flogge]

As far as I remember, there are customizable renderers in HighCharts but they require native JS which is not allowed to be used in Apps due to stability and security reasons.
However, a CustomComponent might come to the rescue. These are tiny add-ins that can encapsulate such logic and are in turn then again configurable via the App’s JSON.
You will probably have to design and code your own when you have specific needs. Maybe the CC section here can help you get started: Custom Components - ONE DATA Community