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

**URL:** <https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988>\
**Category:** Ignition\
**Tags:** perspective, ignition81\
**Created:** [August 22, 2024, 4:51am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988 "2024-08-22T04:51:08Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [August 22, 2024, 4:51am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988/1 "2024-08-22T04:51:08Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/f/7f6a6840413bc67b650009ff349efad668b7428a.png)

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...

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/3/f3e2afa4d565b880e684af63cb94075bb8c6e2ac.png)

Thanks in advance for your help!

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [August 22, 2024, 8:20am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988/2 "2024-08-22T08:20:12Z")

</div>

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`

---

<div class="post-metadata">

**Author:** ![Ujwal\_Wankhede](https://avatars.discourse-cdn.com/v4/letter/u/4af34b/32.png) [@Ujwal\_Wankhede](https://forum.inductiveautomation.com/u/Ujwal_Wankhede)\
**Post date:** [August 23, 2024, 4:56am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988/3 "2024-08-23T04:56:12Z")

</div>

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.

 ![Duration on Y Axis](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/0/50dac03909ae3c387ba005b99c189f13fdf9648b.png)

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

> **XY Component**
>
> ```python
> [
> {
> "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](https://www.amcharts.com/demos-v4/duration-on-value-axis-v4/)**
>
> This demo showcases several powerful concepts. Let’s take a look. Duration axis There’s a special type of axis that can reformat the values into any kind of “duration”. The units and formats are fully configurable. Multiple axis The chart you see...

---

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [August 23, 2024, 5:59am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988/4 "2024-08-23T05:59:31Z")

</div>

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

```python
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)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/5/85ea41f798cb3181bd72ba3ae111be0d5149393d.png)

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:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/0/d05c9540d3dc89ca994a8279d2b31d060a7174c0.png)

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

```python
[
  {
    "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!

---

<div class="post-metadata">

**Author:** ![Ujwal\_Wankhede](https://avatars.discourse-cdn.com/v4/letter/u/4af34b/32.png) [@Ujwal\_Wankhede](https://forum.inductiveautomation.com/u/Ujwal_Wankhede)\
**Post date:** [August 23, 2024, 6:17am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988/5 "2024-08-23T06:17:53Z")

</div>

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

 ![Duration on Y Axis Settings](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/6/c6c6572592b2783050df85207c12de0dfe0fcb33.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [August 23, 2024, 7:33am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988/6 "2024-08-23T07:33:27Z")

</div>

Instead of this, which returns a string,

```python
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 ...

```python
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.

---

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [August 28, 2024, 7:30am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988/7 "2024-08-28T07:30:30Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/1/d1ffd3c3fd7e2a0773b226ecb8aae42f5f4306f6.png)

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.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/4/24fa0c0b7907b676bc1efe6fba63ff4b80d34066.png)

Here is the configuration I am using for the tooltip:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/0/30c009d515efefb24cfe19805794d8d8f7271e26.png)

Any idea on how to fix this?

Thanks in advance!

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [August 28, 2024, 9:57am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988/8 "2024-08-28T09:57:38Z")

</div>

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

> [@Perspective XY Chart xAxes tooltip text {dateX}](https://forum.inductiveautomation.com/t/perspective-xy-chart-xaxes-tooltip-text-datex/53580/12):
>
> [Formatting Strings – amCharts 4 Documentation](https://www.amcharts.com/docs/v4/concepts/formatters/formatting-strings/)] They mention getting the high and low values. I have not been able to get that to work. Have you been able to? [image]

---

<div class="post-metadata">

**Author:** ![Olli\_Burr](https://avatars.discourse-cdn.com/v4/letter/o/9de0a6/32.png) [@Olli\_Burr](https://forum.inductiveautomation.com/u/Olli_Burr)\
**Post date:** [August 28, 2024, 10:52am UTC](https://forum.inductiveautomation.com/t/xy-chart-change-format-of-value-on-y-axis/94988/9 "2024-08-28T10:52:13Z")

</div>

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.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/2/820cd068730dd182f3b669f0eff740c94927aa42.png)

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')}`''.