Hi Phil,

I have included a formula AVERAGE in the excel report. I have no issue running the excel report. However when I change the formula from AVERAGE to AVERAGEIF in the excel report, I encounter error in running the excel report. Error message is "Failure: Error in evaluating cell Data!D2". Cell Data!D2 is where the formula resides.

Do you know the reason?

Thanks.

Felicia

Hi Phil,

Is there anyway to make sure that the meta point calculation is based on the value that is logged instead of using cache values which is updating every 3 seconds?

Felicia

Hi Phil,

I get what you mean by computed live or a history that has been regenerated. To reply your question, the data is computed live.

Felicia

Hi Phil,

I don't quite understand what do you mean computed live or a history that has been regenerated. Basically, I had setup some virtual points and meta points. The meta point is calculated using virtual points. I am checking if the meta calculation in Mango is correct by doing manual calculation after exporting the points.

For example,
Virtual point:

• Point 1
• Point 2

Meta point:

• (Point 1 + Point 2)x2

If Point 1 is 10 and Point 2 is 20, the meta point should be (10+20)x2 = 60

I had extracted the data using Excel reports for past 30 minutes and then do a manual calculation to do a comparison. I am not sure if this is what you mean by computed live.

FYI, I had made the change to delay by 1 second. The result is worst than no delay meaning more variances.

I had been trying a lot of scenarios but failed.

Any other better solution to the setup in order to achieve the expected result? This is important as the data will be exported out. I need to make sure it is correct when doing manual calculation else the report will not be reliable.

Thanks.

Felicia

Hi Phil,

Thanks for your prompt reply. Sorry for the typo error. It should be every 3 seconds instead of every 3 minutes.

I had removed the timestamp and it seems to resolve the current issue I am facing. However, it doesn't resolve the other issue that I am having for the manual calculation. The result of meta point is not the same as manually calculated. The result will be out occasionally. Below is my result for the calculation:

Time Manual Cal From Mango Variance
10 05 2018 13:27 543.23495708 543.23495708 0.00000000
10 05 2018 13:28 549.48000893 549.48000893 0.00000000
10 05 2018 13:29 535.12357546 535.12357546 0.00000000
10 05 2018 13:30 539.55963913 539.55963913 0.00000000
10 05 2018 13:31 532.14960735 532.14960735 0.00000000
10 05 2018 13:32 554.78660457 554.78660457 0.00000000
10 05 2018 13:33 532.78220782 532.78220782 0.00000000
10 05 2018 13:34 561.12353174 553.76835358 7.35517816
10 05 2018 13:35 551.65939993 551.65939993 0.00000000
10 05 2018 13:36 540.21521998 540.21521998 0.00000000
10 05 2018 13:37 540.04708387 540.04708387 0.00000000
10 05 2018 13:38 529.39945998 529.39945998 0.00000000
10 05 2018 13:39 530.50840625 536.65520416 -6.14679791
10 05 2018 13:40 539.35237634 539.35237634 0.00000000
10 05 2018 13:41 585.95006258 585.95006258 0.00000000
10 05 2018 13:42 572.85304086 572.85304086 0.00000000
10 05 2018 13:43 579.10346793 579.10346793 0.00000000
10 05 2018 13:44 566.22326928 561.96256136 4.26070792
10 05 2018 13:45 545.42791329 545.42791329 0.00000000
10 05 2018 13:46 579.51836544 579.51836544 0.00000000
10 05 2018 13:47 562.71974977 562.71974977 0.00000000
10 05 2018 13:48 596.57930691 596.57930691 0.00000000
10 05 2018 13:49 606.39143280 606.39143280 0.00000000
10 05 2018 13:50 610.66484066 610.66484066 0.00000000
10 05 2018 13:51 588.74662065 588.74662065 0.00000000
10 05 2018 13:52 561.65815802 561.65815802 0.00000000
10 05 2018 13:53 541.38559756 541.38559756 0.00000000
10 05 2018 13:54 552.09730558 552.09730558 0.00000000
10 05 2018 13:55 535.06272974 535.06272974 0.00000000
10 05 2018 13:56 527.84615008 527.84615008 0.00000000
10 05 2018 13:57 531.92463244 531.92463244 0.00000000
10 05 2018 13:58 551.87285383 551.87285383 0.00000000
10 05 2018 13:59 569.47098284 569.47098284 0.00000000
10 05 2018 14:00 585.64340645 585.64340645 0.00000000
10 05 2018 14:01 588.84734714 588.84734714 0.00000000
10 05 2018 14:02 600.76407692 600.76407692 0.00000000
10 05 2018 14:03 606.30053705 606.30053705 0.00000000
10 05 2018 14:04 605.83226834 605.56736162 0.26490672
10 05 2018 14:05 603.11035797 602.20232108 0.90803689

Any idea to fix this issue?

Felicia

Hi Phil,

Definitely there is a value in MainHeader1 as the polling is every 3 minutes. However, the timestamp 1 minute interval. Therefore, the timestamp for meta points should be constant. I do not understand why the timestamp for meta point is not constant because it should be based on the timestamp of virtual point.

Is there any other setup to make sure both virtual and meta timestamp is the same? The meta point value should be based on the value of virtual point.

Thanks.

Regards,
Felicia

Hi Phil,

I don't think there is any context points of this meta point have a value at 16:51:11. The meta point is executed on Context update. Therefore most of the timestamp of the meta point is per the virtual point. The polling rate for virtual data source is 3 seconds. Below is one of the example of the meta point:

{
"dataPoints":[
{
"xid":"PlantOperation2",
"name":"Condenser Heat Reject RT",
"enabled":true,
"loggingType":"ON_TS_CHANGE",
"intervalLoggingPeriodType":"MINUTES",
"intervalLoggingType":"INSTANT",
"purgeType":"YEARS",
"pointLocator":{
"dataType":"NUMERIC",
"updateEvent":"NONE",
"contextUpdateEvent":"CONTEXT_LOGGED",
"context":[
{
"varName":"MainHeader1",
"dataPointXid":"MainHeader1",
"updateContext":true
},
{
"varName":"MainHeader2",
"dataPointXid":"MainHeader2",
"updateContext":false
},
{
"varName":"MainHeader3",
"dataPointXid":"MainHeader3",
"updateContext":false
},
{
"varName":"MainHeader4",
"dataPointXid":"MainHeader4",
"updateContext":false
},
{
"varName":"MainHeader5",
"dataPointXid":"MainHeader5",
"updateContext":false
},
{
"varName":"MainHeader6",
"dataPointXid":"MainHeader6",
"updateContext":false
},
{
"varName":"PowerInput1",
"dataPointXid":"PowerInput1",
"updateContext":false
},
{
"varName":"PowerInput2",
"dataPointXid":"PowerInput2",
"updateContext":false
},
{
"varName":"PowerInput3",
"dataPointXid":"PowerInput3",
"updateContext":false
},
{
"varName":"PowerInput4",
"dataPointXid":"PowerInput4",
"updateContext":false
}
],
"logLevel":"NONE",
"variableName":"PlantOperation2",
"executionDelaySeconds":0,
"script":"TIMESTAMP = MainHeader1.time; return(MainHeader6.value4.19(MainHeader5.value-MainHeader4.value )/3.517);",
"scriptPermissions":{
"customPermissions":"",
"dataPointReadPermissions":"superadmin",
"dataPointSetPermissions":"superadmin",
"dataSourcePermissions":"superadmin"
},
"settable":false,
"updateCronPattern":""
},
"eventDetectors":[
],
"plotType":"SPLINE",
"rollup":"NONE",
"unit":"",
"simplifyType":"NONE",
"chartColour":"",
"chartRenderer":null,
"dataSourceXid":"MS_Building Cooling",
"defaultCacheSize":1,
"deviceName":"PlantOperation",
"discardExtremeValues":false,
"discardHighLimit":1.7976931348623157E308,
"discardLowLimit":-1.7976931348623157E308,
"intervalLoggingPeriod":1,
"intervalLoggingSampleWindowSize":0,
"overrideIntervalLoggingSamples":false,
"preventSetExtremeValues":false,
"purgeOverride":false,
"purgePeriod":1,
"readPermission":"",
"setExtremeHighLimit":1.7976931348623157E308,
"setExtremeLowLimit":-1.7976931348623157E308,
"setPermission":"",
"tags":{
},
"textRenderer":{
"type":"ANALOG",
"useUnitAsSuffix":true,
"unit":"",
"renderedUnit":"",
"format":"0.00"
},
"tolerance":0.0
}
]
}

Please let me know if my setup is not correct. My requirement is to have same row for virtual and meta points without roll up and the calculation should be of meta points should be correct if compare to my manual calculation.

Thank you.

Regards,
Felicia

Hi,

I am currently having an issue related to Meta Point. Appreciate your guidance.

I had do the following setup:

• create 1 virtual data source & 1 meta data source.
• create 10 datapoints in virtual data source.
• create several datapoints in meta data source. My script will use the datapoints created in virtual data source. I had also included TIMESTAMP = XXXX.time so that the timestamp of the meta point is the same as virtual point.
• create excel report to download the 10 datapoints in virtual data source and a few datapoints in meta source.
• run the excel report

After download the excel report from Mango, I had included the formulas manually (same formula in the script for meta datapoints) in the excel report to check if the meta points produced the same results as the formulas I had created in the excel report.

I had realized that the figures in meta points do not tie to my manual calculated figures occasionally.

I had checked and realized that the meta point timestamp differs occasionally and this may be the trigger point for the variance. I do not know why the timestamp for meta point will differ (for example, 16:51:11) occasionally from the standard 16:xx:13 which is the same timestamp for virtual datapoints.

Historical for one of the meta point:
Value Time Annotation
0.61kW/RT 16:54:13
0.62kW/RT 16:53:13
0.63kW/RT 16:52:13
0.66kW/RT 16:51:11
0.67kW/RT 16:50:13
0.67kW/RT 16:49:13
0.64kW/RT 16:48:13
0.65kW/RT 16:47:13
0.66kW/RT 16:46:13
0.61kW/RT 16:45:13

Thanks.

Regards,
Felicia

Hi,

Attached below for my script for creating a pie chart. I have 2 issues which I do not know how to do. Appreciate you can assist me.

1. How to show datapoint value into the pie chart? Currently, it can be included by hardcoding it under "text:" However, I will like this to link to datapoint value instead of hardcoding it. I had put it as "text : myPoint.value" or "text : {myPoint.value}", it doesn't work. You can find this in the script provided below under section where "allLabels".

2. How to show a value which is derived using formula into the pie chart? The formula is basically using the datapoint / 144 x 100. Refer to "text : 'Overall Efficiency 87% actual / target x 100' ".

Script:

<ma-get-point-value point-xid="CRWPOE_KP_C01" point="myPoint"></ma-get-point-value>

<p>The point name is "{{myPoint.name}}" and its value is {{myPoint.renderedValue}}.</p>

<ma-pie-chart style="height: 300px; width: 600px" values="[ { value: myPoint.value, text: 'Actual', color: '#942192' }, { value: 2-myPoint.value, text: 'Gap', color: '#ffffff' } ]"
options="{
titles: [{
text: '',
size: 15
}],

depth3D:0,
angle:0,
innerRadius: '60%',
allLabels: [{
y: '52%' ,
align: 'center' ,
size: 25,
bold : 'true',
text : '99',
color : '#555',

}, {
y : '44%' ,
align : 'center' ,
size : 12,
text : myPoint.value,
color : '#555'}, {
y : '20%' ,
align : 'center' ,
size : 25,
bold : 'true',
text : 'Overall Efficiency 87% actual / target x 100' ,
color : '#555'}]
}" ></ma-pie-chart>

Thanks.

Regards,
Felicia

@phildunlap,

Thanks, it works! It does make a difference between small capitals and big capitals.

Regards,
Felicia

Hi Phildunlap,

Just realized my mistake. I had amended according to your screen. The result still the same. The time is not the same as COWSFS_FM_C01. It is out by 20 seconds. Is this the norm? Possible to be the exact per COWSFS timestamp?

The result as follows:
COWSFS_FM_C01 16:57:56
COHRCP 16:57:36

Regards,
Felicia

Hi Phildunlap,

I had used the stated formula given but the issue still continues. Perhaps there is something missing in my script somewhere. Below is my script for meta source datapoint COHRCP:

Timestamp = COWSFS_FM_C01.value;
return COWSFS_FM_C01.value * 1;

Please check if my script is correct. If it is correct, is there anything else I need to check to make sure the script works?

Thanks.

Hi Phildunlap,

Thanks for the prompt reply.

Basically, I will be using XID to do some naming which may be very lengthy. Therefore, I am thinking using ID instead since it is just numeric & short. Just want to see if it is possible.

Regards,
Felicia

I had copied the following script from API Docs>Directives>ma-json-store and tried it.

<ma-json-store xid="phoneData" item="myItem" value="myValue"></ma-json-store>
<input ng-model="myValue.phone">
<md-button class="md-raised md-primary md-hue-3" ng-click="myItem.$save()">
<md-icon>save</md-icon> Save
</md-button>
<p>Phone # from JSON store: {{myItem.jsonData.phone}}</p>

I realized that each time I rename XID to a different name, a different ID is created in jsondata. I detected this when I used SQL to query out the data from Jsondata using this script "select * from jsondata".

Question:

1. How to I rename XID to a different name without deleting ID?

For example, Jsondata may give an ID of 36 with XID "Name". I want to change the ID 36 XID from "Name" to "Address" without deleting ID 36.

Current:
ID XID
36 Name

Change to:
ID XID
36 Address

2. For the above script, is it possible to ID instead XID? Does the following script work? I had tried but failed. Perhaps I had missed out something.

<ma-json-store id="36" item="myItem" value="myValue"></ma-json-store>
<input ng-model="myValue.phone">
<md-button class="md-raised md-primary md-hue-3" ng-click="myItem.$save()">
<md-icon>save</md-icon> Save
</md-button>
<p>Phone # from JSON store: {{myItem.jsonData.phone}}</p>

3. For ID, is it possible to assign the number manually instead of letting system to generate ID? If yes, how can I do it?

Thanks.

Hi Joel Hagger,

I had already done so. I believe is the timestamp issue. To be more specific, I will illustrate with an example since my previously email is not clear.

Data point A (Virtual) Timestamp is 21:01:30
Data point B (Virtual) Timestamp is 21:01:30
Data point C (Meta) Timestamp is 21:02:00. This may be due to the fact that this is a formula using Data point A add Data point B and there is a delay. Therefore, the timestamp is 1 minute later than Data point A & B.

How can I make Data point C Timestamp to be not later than 21:01:59 so that the 3 data can be displayed as 1 row?

Thanks.

Regards,
Felicia

Hi Phildunalp,

I had changed previously checked the named ranges and they are all correct.

Regards,
Felicia

Hi,

Please enlighten me how to resolve this issue I had encountered.

I had created a few data points for testing:

• Data point A using Data Source Type = Virtual
• Data point B using Data Source Type = Virtual
• Data point C using Data Source Type = Meta. I had included a formula using Data point A + Data point B

I had used excel reports. The data export had showed me the following data:

Timestamp Data point A Data Point B Data Point C
42748.76389
42748.76458 6.774346083 12.64840074
42748.76528 6.743774728 12.66065828 19.42274682

The sum of Data point A & Data point B in 2nd row is displaced in Data point C but in 3rd row.

I need to resolve:

• the result (data point c) to be displayed in 2nd row
• remove the first blank row

Appreciate you can let me know how to resolve this.

FYI, I had used Rollup function (First 1 Minutes) in Excel Reports.

I suspect that a different timestamp is used for data point C and that timestamp may be a minute after data point A & B.

Thanks.

Regards,
Felicia