Hello Everybody,
I need a help with trends in perspective 8.1.36
I have a database table as shown below consisting of three columns, Timestamp, Counts & Roll ID, I would like to show the counts for in Roll ID based on time selected using this table.
As new RolID gets logged in table it should dynamically add them in Chart PROPS and shown the new trends (if it fits the time scale selection).
DB used is PostgreSQL
Any help/guidance or point to right direction would be helpful, Thanks !
That's going to be a bit of work.
I suggest that you use an XY Chart.
- Get the data in JSON format. It should be something like this:
[
{'t_stamp': '2024-03-06 16:47', 'Roll 1': 1},
{'t_stamp': '2024-03-06 17:02', 'Roll 1': 2},
{'t_stamp': '2024-03-06 17:19', 'Roll 1': 3},
{'t_stamp': '2024-03-06 18:00', 'Roll 1': 4},
{'t_stamp': '2024-03-06 18:24', 'Roll 1': 5},
{'t_stamp': '2024-03-06 18:43', 'Roll 1': 6},
{'t_stamp': '2024-03-06 19:04', 'Roll 1': 7},
{'t_stamp': '2024-03-06 19:20', 'Roll 1': 8},
{'t_stamp': '2024-03-06 20:01', 'Roll 2': 1},
{'t_stamp': '2024-03-06 20:41', 'Roll 2': 2},
{'t_stamp': '2024-03-06 21:19', 'Roll 2': 3},
{'t_stamp': '2024-03-06 21:41', 'Roll 2': 4},
{'t_stamp': '2024-03-06 22:03', 'Roll 2': 5},
{'t_stamp': '2024-03-06 22:46', 'Roll 2': 6},
{'t_stamp': '2024-03-06 23:26', 'Roll 2': 7},
{'t_stamp': '2024-03-06 23:55', 'Roll 3': 1},
{'t_stamp': '2024-03-07 00:16', 'Roll 3': 2},
{'t_stamp': '2024-03-07 00:44', 'Roll 3': 3},
{'t_stamp': '2024-03-07 01:09', 'Roll 3': 4}
]
Note that I've added the word "Roll " into the Roll column to help with identification in the chart. You can paste this in as a new datasource for experimentation.
- You'll need to script the series to generate a series for each roll. That may require a query of the form,
SELECT UNIQUE CONCAT("Roll ", RollID) FROM ...
Try pasting this into an empty view.
Roll XY Chart
[
{
"type": "ia.chart.xy",
"version": 0,
"props": {
"xAxes": [
{
"name": "time",
"label": {
"enabled": true,
"text": "Time",
"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": false,
"timeUnit": "hour",
"count": 1,
"skipEmptyPeriods": false
},
"range": {
"max": "",
"min": "",
"useStrict": false
},
"break": {
"enabled": false,
"startDate": "",
"endDate": "",
"size": 0.05
},
"inputFormat": "yyyy-M-d HH:mm",
"format": "HH:mm"
},
"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": 1,
"dashArray": "",
"minDistance": 60,
"position": 0.5
},
"font": {
"size": "",
"weight": 500
}
}
}
],
"yAxes": [
{
"appearance": {
"font": {
"size": "",
"weight": 500
},
"grid": {
"color": "",
"dashArray": "4,4",
"minDistance": null,
"opacity": 1,
"position": 0.5
},
"inside": false,
"labels": {
"color": "",
"horizontalCenter": "middle",
"opacity": 1,
"rotation": 0,
"verticalCenter": "middle"
},
"opposite": false
},
"category": {
"break": {
"enabled": false,
"endCategory": "",
"size": 0.05,
"startCategory": ""
}
},
"date": {
"baseInterval": {
"count": 1,
"enabled": false,
"skipEmptyPeriods": false,
"timeUnit": "hour"
},
"break": {
"enabled": false,
"endDate": "",
"size": 0.05,
"startDate": ""
},
"format": "M/d/yyyy",
"inputFormat": "yyyy-MM-dd kk:mm:ss",
"range": {
"max": "",
"min": "",
"useStrict": false
}
},
"inversed": false,
"label": {
"color": "",
"enabled": true,
"text": "Count"
},
"name": "count",
"render": "value",
"tooltip": {
"background": {
"color": "",
"opacity": 1
},
"cornerRadius": 3,
"enabled": true,
"pointerLength": 4,
"text": ""
},
"value": {
"break": {
"enabled": false,
"endValue": 100,
"size": 0.05,
"startValue": 0
},
"format": "#,###.##",
"logarithmic": false,
"range": {
"max": "",
"min": "",
"useStrict": false
}
},
"visible": true
}
],
"series": [
{
"name": "Roll 1",
"label": {
"text": "Roll 1"
},
"visible": true,
"hiddenInLegend": false,
"defaultState": {
"visible": true
},
"data": {
"source": "rollData",
"x": "t_stamp",
"y": "Roll 1"
},
"xAxis": "time",
"yAxis": "count",
"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": "green",
"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": false,
"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": ""
}
}
]
}
}
},
{
"name": "Roll 2",
"label": {
"text": "Roll 2"
},
"visible": true,
"hiddenInLegend": false,
"defaultState": {
"visible": true
},
"data": {
"source": "rollData",
"x": "t_stamp",
"y": "Roll 2"
},
"xAxis": "time",
"yAxis": "count",
"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": "green",
"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": "#1AC218",
"dashArray": ""
},
"fill": {
"opacity": 0,
"color": ""
},
"bullets": [
{
"enabled": false,
"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": ""
}
}
]
}
}
},
{
"name": "Roll 3",
"label": {
"text": "Roll 3"
},
"visible": true,
"hiddenInLegend": false,
"defaultState": {
"visible": true
},
"data": {
"source": "rollData",
"x": "t_stamp",
"y": "Roll 3"
},
"xAxis": "time",
"yAxis": "count",
"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": "green",
"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": "#D7221A",
"dashArray": ""
},
"fill": {
"opacity": 0,
"color": ""
},
"bullets": [
{
"enabled": false,
"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": {
"rollData": [
{
"t_stamp": "2024-03-06 16:47",
"Roll 1": 1
},
{
"t_stamp": "2024-03-06 17:02",
"Roll 1": 2
},
{
"t_stamp": "2024-03-06 17:19",
"Roll 1": 3
},
{
"t_stamp": "2024-03-06 18:00",
"Roll 1": 4
},
{
"t_stamp": "2024-03-06 18:24",
"Roll 1": 5
},
{
"t_stamp": "2024-03-06 18:43",
"Roll 1": 6
},
{
"t_stamp": "2024-03-06 19:04",
"Roll 1": 7
},
{
"t_stamp": "2024-03-06 19:20",
"Roll 1": 8
},
{
"t_stamp": "2024-03-06 20:01",
"Roll 2": 1
},
{
"t_stamp": "2024-03-06 20:41",
"Roll 2": 2
},
{
"t_stamp": "2024-03-06 21:19",
"Roll 2": 3
},
{
"t_stamp": "2024-03-06 21:41",
"Roll 2": 4
},
{
"t_stamp": "2024-03-06 22:03",
"Roll 2": 5
},
{
"t_stamp": "2024-03-06 22:46",
"Roll 2": 6
},
{
"t_stamp": "2024-03-06 23:26",
"Roll 2": 7
},
{
"t_stamp": "2024-03-06 23:55",
"Roll 3": 1
},
{
"t_stamp": "2024-03-07 00:16",
"Roll 3": 2
},
{
"t_stamp": "2024-03-07 00:44",
"Roll 3": 3
},
{
"t_stamp": "2024-03-07 01:09",
"Roll 3": 4
}
]
}
},
"meta": {
"name": "XYChart"
},
"position": {
"basis": "476px"
},
"custom": {
"sqlResult": [
{
"t_stamp": 1709743620000,
"Count": 1,
"RollID": "Roll 1"
},
{
"t_stamp": 1709746096701,
"Count": 2,
"RollID": "Roll 1"
},
{
"t_stamp": 1709748766661,
"Count": 3,
"RollID": "Roll 1"
},
{
"t_stamp": 1709749919414,
"Count": 4,
"RollID": "Roll 1"
},
{
"t_stamp": 1709750892807,
"Count": 5,
"RollID": "Roll 1"
},
{
"t_stamp": 1709752423674,
"Count": 6,
"RollID": "Roll 1"
},
{
"t_stamp": 1709755034731,
"Count": 7,
"RollID": "Roll 1"
},
{
"t_stamp": 1709756487570,
"Count": 8,
"RollID": "Roll 1"
},
{
"t_stamp": 1709758071533,
"Count": 9,
"RollID": "Roll 2"
},
{
"t_stamp": 1709760770040,
"Count": 10,
"RollID": "Roll 2"
},
{
"t_stamp": 1709762054662,
"Count": 11,
"RollID": "Roll 2"
},
{
"t_stamp": 1709763717697,
"Count": 1,
"RollID": "Roll 2"
},
{
"t_stamp": 1709766197577,
"Count": 2,
"RollID": "Roll 2"
},
{
"t_stamp": 1709767188908,
"Count": 3,
"RollID": "Roll 2"
},
{
"t_stamp": 1709769054979,
"Count": 4,
"RollID": "Roll 2"
},
{
"t_stamp": 1709771292439,
"Count": 5,
"RollID": "Roll 3"
},
{
"t_stamp": 1709773497640,
"Count": 6,
"RollID": "Roll 3"
},
{
"t_stamp": 1709774987668,
"Count": 7,
"RollID": "Roll 3"
},
{
"t_stamp": 1709776905435,
"Count": 8,
"RollID": "Roll 3"
}
]
}
}
]
1 Like
Consider using the "DB Table History Provider" to just drop these into a PowerChart.
3 Likes
Thanks ! Will try.
Thanks @pturmel , but wouldn't it require each Roll ID have separate column to show individual trends ?
No, you can use a key to segregate trends in the same table.
Thank you @pturmel I managed to achieve my objective
First I created a Query tag - 1 sec poll and retrieved a RollID column as Dataset and then used that in below code to loop through and append Pen PROP on Power chart.
Value = system.tag.readBlocking('[default]Enterprise/New Tag 1')[0].value
RollID = ''
pens = []
for row in range(Value.getRowCount()):
for col in range(Value.getColumnCount()):
RollID = Value.getValueAt(row, col)
tagPath ='histprov:Valve Consumption:/table:"Consumption":/column:Count:/timestamp:TimeStamp:/keycolumn:RollID:/keyvalue:' + str(RollID)
pens.append(
{
"name": str(RollID), #name,
"visible": True,
"enabled": True,
"selectable": True,
"axis": "Axis 1",
"plot": 0,
"display": {
"type": "line",
"interpolation": "curveLinear",
"breakLine": True,
"radius": 3,
"styles": {
"normal": {
"stroke": {
"color": "#DE7E41",
"width": 1,
"opacity": 0.8,
"dashArray": 0
},
"fill": {
"color": "#DE7E41",
"opacity": 0.8
}
},
"highlighted": {
"stroke": {
"color": "#DE7E41",
"width": 1,
"opacity": 1,
"dashArray": 0
},
"fill": {
"color": "#DE7E41",
"opacity": 1
}
},
"selected": {
"stroke": {
"color": "#DE7E41",
"width": 1,
"opacity": 1,
"dashArray": 0
},
"fill": {
"color": "#DE7E41",
"opacity": 1
}
},
"muted": {
"stroke": {
"color": "#DE7E41",
"width": 1,
"opacity": 0.4,
"dashArray": 0
},
"fill": {
"color": "#DE7E41",
"opacity": 0.4
}
}
}
},
"data": {
"source": tagPath,
"aggregateMode": "Average"
}
}
)
self.getSibling("PowerChart").props.pens = pens