🍉 Meet Flexmonster Pivot Table & Charts 2.9.Check out all hot features!
Get Free Trial

How to convert seconds to HH:MM format for Highcharts side

Denis asked on April 6, 2020

We are integrating Flexmonster with Highcharts.
We have a problem with formatting time type fields. Values of this fields are stored in seconds and we need to apply HH:MM formatting for this fields on all types of charts, including column, bar, line, scatter and pie. Currently, this fields are displayed as seconds on charts. The main difficulty is that Flexmonster preparing the data for the highcharts returns only the values of fields without any type.
So, we tried several solutions:

  1. Configure formatting on Highcharts side. In a dubious way, we found time fields and apply formatting with converting second to HH:MM format. However, there are many places and cases that can’t be processed. Sometimes it’s even impossible to apply this format without knowing the type of columnю row or measure. For example, with complex series name on column type chart, or with tooltips on scatter and pie charts. As result, it’s wrong solution.
  2. Prepare data for each type of chart at prepareDataFunction(…). So, here we can try to add a “type” property with “time” value for time fields or just already here convert seconds to HH:MM format (i’m not sure both decisions are good) and transfer this data to highchats. So, in such cases we need to apply one of these solutions without overriding flexmonster’s prepareDataFunction(…) for our chart types.

Perhaps you can give some advice on each solution or help apply a new one.
Thanks in advance,

3 answers

Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 7, 2020

Hello, Denis,
Thank you for reaching out to us.
Our team would like to kindly recommend using the following approach in order to set an appropriate time formatting for Highcharts:
The data object passed to Highcharts has the structure of the chart object described in Hightcharts API Reference. It means it can be modified in the desired way before being passed to the handler.
In order to cover your requirement to display seconds in HH:mm format as Flexmonster does, we recommend format labels of an appropriate axis with a custom function. It is also important to customize the tooltip in the way it contains formatted values as well.
We have prepared an example demonstrating the described approach.
Please note that implementing the formatting for the pie chart can require additional modifications of the data object.
Finally, in order to have the possibility to get information about the type of the specific hierarchy, it is possible to use the mapping object. The Mapping Object allows defining field data types, captions, and multi-level hierarchies. It is possible to retrieve such an object using the getReport method and apply appropriate changes based on data from it.
Detailed information about the mapping object and its properties can be found in our documentation. Also, we recommend checking out the page dedicated to the getReport API.
We hope it helps.
Do not hesitate to contact us in case additional questions occur.
Best regards,

Denis April 9, 2020

Thank you for your answer, Illia
As i described above, there are some specific and problem cases with this approach.
It’s not tough to apply conditional formatting for seconds on axis at charts, however there are some difficulties with specific series and tooltip.
For example, with “complex” values like “value-value”, see attached picture, please.
In such case we need to apply formatting for time values at series.name and correspondingly at tooltip.
Can you give some advices for this specific case?

Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 10, 2020

Hello, Denis,
Thank you for your feedback.
In case more than one measure is used and the order is unknown, we recommend considering using the following approach:
Flexmonster provides the getMeasures API call that returns a list of the selected measures in the report. Measures are represented as objects with specific properties, including the name of the measure and its data type.
Therefore, it is possible to scan all selected measures before modifying the data object and choose those to which the time formatting should be applied.
Please see an example we have prepared for you. It uses a sample data set created basing on the screenshot you have provided.
Our team would like to provide some additional explanation about following code snippets which are parts of the applyTimeFormatting function (starting on line 63):

function applyTimeFormatting(data) {

  let measures = pivot.getMeasures();
  let timeFormatted = [];
  for (let measure of measures)
    if (measure.type == 'time')

This block of code is responsible for retrieving names of measures which data type is time and saving them to the timeFormatted array.
Such an array is used in the following part of the function:

for (let axis of data.yAxis)
    if (timeFormatted.includes(axis.title.text))
      axis.labels = {
        formatter: function() {
          return formatter(this.value);

It filters all elements of the yAxis array and applies an appropriate formatting in case the title of an axis can be found in the timeFormatted array.
Finally, the tooltip needs to be customized:

data.tooltip = {
    useHTML: true,
    formatter: function() {
      let value;
      if (timeFormatted.includes(this.point.series.name))
        value = formatter(this.y);
      else value = this.y;
      return `${this.point.category}<br>${this.point.series.name}: <b>${value}</b>`;

It uses a similar algorithm and format the value only in case its name is mentioned in the timeFormatted array.
The described approach allows applying formatting despite the order of measures and their quantity. It can be used in order to apply formatting for other data types as well.
We would like to draw your attention to the fact that the provided example is quite general. Additional modifications may be required for your case; however, it illustrates the general idea that can be used as a template.
We hope it helps.
Please contact us in case any additional questions appear.
Best regards,

Please login or Register to Submit Answer