XY Chart - Change Format of "value" on "Y-Axis"

[Olli_Burr]

Hi folks,

I've been trying for too long to adjust the format on the Y-axis in an XY chart (see image below), unfortunately without success. It's probably super simple, but I'm currently running out of ideas... I've tried various bindings, but it seems that the preset formats are not customizable, at least I have no idea how to do it.

Now the question to the community is whether anyone has an idea of how to adjust the format so that the value is always displayed as "hh:mm" (hours and minutes).

For information - the value passed to the "Value" comes from an SQL script (see image below) already in the format "hh:mm". Unfortunately, this format is not being adopted...

Thanks in advance for your help!

[Transistor]

Timestamps should always be stored and passed as datetime objects. Formatting should only be applied at the point of display.

If you can fix your SQL then,
yAxes.0.render : date
yAxes.0.date.format : HH:mm

[Ujwal_Wankhede]

If I understand correctly, you're asking about representing duration on the Y-axis.
Thanks for bringing this up, it prompted me to explore the various ways this data can be visualized on screen.

Below, I've included the JSON configuration for the component above, which should help you get started.

XY Component

[
  {
    "type": "ia.chart.xy",
    "version": 0,
    "props": {
      "xAxes": [
        {
          "name": "time",
          "label": {
            "enabled": true,
            "text": "Month",
            "color": ""
          },
          "visible": true,
          "tooltip": {
            "enabled": true,
            "text": "",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "inversed": false,
          "render": "category",
          "category": {
            "break": {
              "enabled": false,
              "startCategory": "",
              "endCategory": "",
              "size": 0.05
            }
          },
          "date": {
            "baseInterval": {
              "enabled": true,
              "timeUnit": "week",
              "count": 1,
              "skipEmptyPeriods": false
            },
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "break": {
              "enabled": false,
              "startDate": "",
              "endDate": "",
              "size": 0.05
            },
            "inputFormat": "yyyy-MM-dd kk:mm:ss",
            "format": "M/d/yyyy"
          },
          "value": {
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "logarithmic": false,
            "break": {
              "enabled": false,
              "startValue": 0,
              "endValue": 100,
              "size": 0.05
            },
            "format": "#,###.##"
          },
          "appearance": {
            "opposite": false,
            "inside": false,
            "labels": {
              "color": "",
              "opacity": 1,
              "rotation": 0,
              "verticalCenter": "middle",
              "horizontalCenter": "middle"
            },
            "grid": {
              "color": "",
              "opacity": 0.5,
              "dashArray": "10px",
              "minDistance": 60,
              "position": 0.5
            },
            "font": {
              "size": "",
              "weight": 500
            }
          }
        }
      ],
      "yAxes": [
        {
          "name": "durationYAxis",
          "label": {
            "enabled": true,
            "text": "Duration",
            "color": ""
          },
          "visible": true,
          "tooltip": {
            "enabled": true,
            "text": "",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "inversed": false,
          "render": "date",
          "category": {
            "break": {
              "enabled": false,
              "startCategory": "",
              "endCategory": "",
              "size": 0.05
            }
          },
          "date": {
            "baseInterval": {
              "enabled": true,
              "timeUnit": "minute",
              "count": 1,
              "skipEmptyPeriods": false
            },
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "break": {
              "enabled": false,
              "startDate": "",
              "endDate": "",
              "size": 0.05
            },
            "inputFormat": "mm:ss",
            "format": "mm'min' ss'sec'"
          },
          "value": {
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "logarithmic": false,
            "break": {
              "enabled": false,
              "startValue": 0,
              "endValue": 100,
              "size": 0.05
            },
            "format": "#,###.##"
          },
          "appearance": {
            "opposite": false,
            "inside": false,
            "labels": {
              "color": "",
              "opacity": 1,
              "rotation": 0,
              "verticalCenter": "middle",
              "horizontalCenter": "middle"
            },
            "grid": {
              "color": "",
              "opacity": 0.5,
              "dashArray": "10px",
              "minDistance": null,
              "position": 0.5
            },
            "font": {
              "size": "",
              "weight": 500
            }
          }
        }
      ],
      "series": [
        {
          "name": "Duration",
          "label": {
            "text": "Month"
          },
          "visible": true,
          "hiddenInLegend": true,
          "defaultState": {
            "visible": true
          },
          "data": {
            "source": "example",
            "x": "date",
            "y": "duration"
          },
          "xAxis": "time",
          "yAxis": "durationYAxis",
          "zIndex": 0,
          "tooltip": {
            "enabled": true,
            "text": "{name}: [bold]{valueY}[/]",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "render": "line",
          "candlestick": {
            "open": {
              "x": "",
              "y": ""
            },
            "high": {
              "x": "",
              "y": ""
            },
            "low": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "fill": {
                "color": "",
                "opacity": 1
              },
              "stroke": {
                "color": "",
                "opacity": 1,
                "width": 1
              },
              "stacked": false,
              "deriveFieldsFromData": {
                "fill": {
                  "color": "",
                  "opacity": ""
                },
                "stroke": {
                  "color": "",
                  "opacity": "",
                  "width": ""
                }
              },
              "heatRules": {
                "enabled": false,
                "max": "",
                "min": "",
                "dataField": ""
              }
            }
          },
          "column": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "fill": {
                "color": "",
                "opacity": 1
              },
              "stroke": {
                "color": "",
                "opacity": 1,
                "width": 1
              },
              "stacked": false,
              "width": null,
              "height": null,
              "deriveFieldsFromData": {
                "fill": {
                  "color": "",
                  "opacity": ""
                },
                "stroke": {
                  "color": "",
                  "opacity": "",
                  "width": ""
                }
              },
              "heatRules": {
                "enabled": false,
                "max": "",
                "min": "",
                "dataField": ""
              }
            }
          },
          "line": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "connect": true,
              "tensionX": 1,
              "tensionY": 1,
              "minDistance": 0.5,
              "stroke": {
                "width": 3,
                "opacity": 1,
                "color": "",
                "dashArray": ""
              },
              "fill": {
                "opacity": 0,
                "color": ""
              },
              "bullets": [
                {
                  "enabled": true,
                  "render": "circle",
                  "width": 10,
                  "height": 10,
                  "label": {
                    "text": "{value}",
                    "position": {
                      "dx": 0,
                      "dy": 0
                    }
                  },
                  "fill": {
                    "color": "",
                    "opacity": 1
                  },
                  "stroke": {
                    "color": "",
                    "opacity": 1,
                    "width": 1
                  },
                  "rotation": 0,
                  "tooltip": {
                    "enabled": true,
                    "text": "{name}: [bold]{valueY}[/]",
                    "cornerRadius": 3,
                    "pointerLength": 4,
                    "background": {
                      "color": "",
                      "opacity": 1
                    }
                  },
                  "deriveFieldsFromData": {
                    "fill": {
                      "color": "",
                      "opacity": ""
                    },
                    "stroke": {
                      "color": "",
                      "opacity": "",
                      "width": ""
                    },
                    "rotation": ""
                  },
                  "heatRules": {
                    "enabled": false,
                    "max": 100,
                    "min": 2,
                    "dataField": ""
                  }
                }
              ]
            }
          },
          "stepLine": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "connect": true,
              "tensionX": 1,
              "tensionY": 1,
              "minDistance": 0.5,
              "stroke": {
                "width": 3,
                "opacity": 1,
                "color": "",
                "dashArray": ""
              },
              "fill": {
                "opacity": 0,
                "color": ""
              },
              "bullets": [
                {
                  "enabled": true,
                  "render": "circle",
                  "width": 10,
                  "height": 10,
                  "label": {
                    "text": "{value}",
                    "position": {
                      "dx": 0,
                      "dy": 0
                    }
                  },
                  "fill": {
                    "color": "",
                    "opacity": 1
                  },
                  "stroke": {
                    "color": "",
                    "opacity": 1,
                    "width": 1
                  },
                  "rotation": 0,
                  "tooltip": {
                    "enabled": true,
                    "text": "{name}: [bold]{valueY}[/]",
                    "cornerRadius": 3,
                    "pointerLength": 4,
                    "background": {
                      "color": "",
                      "opacity": 1
                    }
                  },
                  "deriveFieldsFromData": {
                    "fill": {
                      "color": "",
                      "opacity": ""
                    },
                    "stroke": {
                      "color": "",
                      "opacity": "",
                      "width": ""
                    },
                    "rotation": ""
                  },
                  "heatRules": {
                    "enabled": false,
                    "max": 100,
                    "min": 2,
                    "dataField": ""
                  }
                }
              ]
            }
          }
        }
      ],
      "dataSources": {
        "example": [
          {
            "date": "2023/05",
            "duration": "05:04"
          },
          {
            "date": "2023/06",
            "duration": "03:04"
          },
          {
            "date": "2023/07",
            "duration": "15:04"
          },
          {
            "date": "2023/08",
            "duration": "10:04"
          }
        ]
      }
    },
    "meta": {
      "name": "XYChart"
    },
    "position": {
      "x": 36,
      "y": 25,
      "height": 375,
      "width": 754
    },
    "custom": {}
  }
]

For deeper dive into additional details check out the resources linked below.

Duration on Value Axis - amCharts

[Olli_Burr]

@Ujwal_Wankhede

yyy-MM-dd kk:mm:ss

Hello Ujwal,

Thank you for your support!

That's correct, I'm trying to represent duration on the Y-axis.

I have adapted your approach to my case as best as possible, but unfortunately, it's not working properly. I must confess that I'm trying everything at this point, hoping to make the right click...

I've taken the liberty of attaching all the data that the XY chart is working with. Maybe you could take a look at it in your free time...

Here's my SQL script with a section of the results:

SELECT 
    CONCAT(DATEPART(year, Date_End_Measurement), '-', RIGHT('0' + CAST(DATEPART(month, Date_End_Measurement) AS VARCHAR(2)), 2)) AS Month,
    LEFT(CAST(DATEADD(SECOND, AVG(DATEDIFF(SECOND, Date_generated, Date_End_Measurement)), '1900-01-01') AS TIME), 5) AS CycleTime
FROM 
    Orders
WHERE 
    Status > 3 AND Order_Text = 'Object' AND Date_End_Measurement BETWEEN :Date_Measurement_start AND :Date_End_Measurement
GROUP BY 
    DATEPART(year, Date_End_Measurement), DATEPART(month, Date_End_Measurement)
ORDER BY 
    DATEPART(year, Date_End_Measurement), DATEPART(month, Date_End_Measurement)

(Left is Year-Month and right is the Cycletime in hh:mm)

The Chart looks rather odd... still no Cycletime visible on the Y-Axis and Bullets are also off. Here's a view of the result in my XY chart:

And here is the configuration data for the Y-Axis:

[
  {
    "name": "CycleTime",
    "label": {
      "enabled": true,
      "text": "Ø Gesamtzeit in (hh:mm)",
      "color": ""
    },
    "inversed": false,
    "visible": true,
    "tooltip": {
      "enabled": true,
      "text": "",
      "cornerRadius": 3,
      "pointerLength": 4,
      "background": {
        "color": "",
        "opacity": 1
      }
    },
    "render": "date",
    "category": {
      "break": {
        "enabled": false,
        "startCategory": "",
        "endCategory": "",
        "size": 0.05
      }
    },
    "date": {
      "baseInterval": {
        "enabled": true,
        "timeUnit": "hour",
        "count": 1,
        "skipEmptyPeriods": false
      },
      "range": {
        "max": "",
        "min": "",
        "useStrict": false
      },
      "break": {
        "enabled": false,
        "startDate": "",
        "endDate": "",
        "size": 0.05
      },
      "inputFormat": "M/d/yyyy HH:mm:ss",
      "format": "HH:mm"
    },
    "value": {
      "range": {
        "max": "",
        "min": 0,
        "useStrict": false
      },
      "logarithmic": false,
      "break": {
        "enabled": false,
        "startValue": 0,
        "endValue": "max",
        "size": 0.05
      },
      "format": "#,###.##"
    },
    "appearance": {
      "opposite": true,
      "inside": false,
      "labels": {
        "color": "",
        "opacity": 1,
        "rotation": 0,
        "verticalCenter": "middle",
        "horizontalCenter": "middle"
      },
      "grid": {
        "color": "",
        "opacity": 0.5,
        "dashArray": "",
        "minDistance": null,
        "position": 0.5
      },
      "font": {
        "size": "",
        "weight": 500
      }
    }
  }
]

Any sort of hint is much appreciated!

Thanks in advance!

[Ujwal_Wankhede]

Try changing inputFormat: HH:mm and it should work.

[Transistor]

Instead of this, which returns a string,

SELECT 
    CONCAT(
        DATEPART(YEAR, Date_End_Measurement), 
        '-', 
        RIGHT('0' + CAST(
            DATEPART(MONTH, Date_End_Measurement) AS VARCHAR(2)
        ), 2)
    ) AS Month,

... try this, which returns a date ...

SELECT 
    DATEFROMPARTS(
        DATEPART(YEAR, Date_End_Measurement), 
        DATEPART(MONTH, Date_End_Measurement),
        0
    ) AS Month,

You should be able to do the same for the time / duration.

DATEFROMPARTS is MS SQL syntax. I'm not sure if it's different in your database engine.

[Olli_Burr]

Thanks for the tips Transistor and Ujwal_Wankhede!

I ended up switching the X-Axis to "category", leavin the SQL-Query as it is. At least the graph now makes sense.

However, I am now having trouble with the tooltip function for the bullets. When I hover over one of the bullets, it only shows the name of the corresponding legend but not the value derived from the Y-Axis.

Here is the configuration I am using for the tooltip:

Any idea on how to fix this?

Thanks in advance!

[Transistor]

I've documented all my "discoveries" about XY Chart tooltips here:

Perspective XY Chart xAxes tooltip text {dateX}

Formatting Strings – amCharts 4 Documentation] They mention getting the high and low values. I have not been able to get that to work. Have you been able to? [image]

[Olli_Burr]

I fixed the error... it was an amateur mistake on my part... instead of working on the tooltip of the bullets, I was working on the tooltip of the legend.

Thanks to your tip, Transistor, or reference to your older posts, I also fixed the corresponding transformation of the passed value ...
{name}: {dateY.formatDate('HH:mm')}''.