How to write javascript code calculate sum of specific column rows data between first and last row?

[RM12]

Hello;

          I want to calculate sum of specific "timestamp" column rows data except first and last row Please suggest how do I write JavaScript code to calculate sum of "timestamp " column rows data except first and last row??

Thanks in advance

[yhan]

Hello @RM12 ,

Before getting to answer of this question, we should first know:

1. If the 'timestamp' is a column of a specific database table? Or if the 'timestamp' is a column of an exsiting infotable?
2. How would you like to caculate sum of 'timestamp', if the timestamp will be converted to number type before caculating? Could to take a example for the caculating?

Thanks,

/Yoyo

[RM12]

Thanks for your reply, I have Database table and getting continuous data with two column name heart rate and timestamp .set Hr ranges between 0-90 ,90-110,110-130,130-150 and 150 above .Suppose in 1 hour how many time heart rate stay in between  ranges. please suggest how do I calculate time between according to heart rate ranges??? 

[yhan · Accepted solution]

Hi @RM12 ,

One way to fulfill this business requirement you can refer:

1. Suppose you have create a service to get the needed query result of this database table to a infotable parameter named tableResult which include 2 columns(heartRange and timestamp);

2. Create a new infotable through a service which will derived one number field for caculating

var tableResult=me.tableResult;
var params = {
types : "NUMBER",
columns : "number", 
t : tableResult,
expressions : "1"
};
// DeriveFields
var result = Resources['InfoTableFunctions'].DeriveFields(params);

3. Create a datashape contains 3 fields - heartRange, timestamp, number 

4. Create a service to caculate:

var tr = me.tableResult();
var st=Date.parse(StartTime); //inputparameter
var et=Date.parse(EndTime); //inputparameter
var params = {
    infoTableName : "queryHeartRange",
    dataShapeName : "queryHeartRangeDatashape"
};
var queryHeartRange = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);
for (var i=0;i<tr.rows.length;i++){
    var ct=Date.parse(tr.rows[i].timestamp);
    if (st<=ct && ct<et){
        queryHeartRange.AddRow(tr.rows[i]);
    }
}
var params1 = {
        columns : "number", 
        aggregates : "SUM",
        t : queryHeartRange};
var totalNumber = Resources['InfoTableFunctions'].Aggregate(params1); 
var result = totalNumber.SUM_number;

You can also add your query logic in above js code.

Best Regards,

/Yoyo

[slangley]

Hi @RM12.

If you feel your question has been answered, please mark the appropriate response as the Accepted Solution for the benefit of others with the same question.

Regards.

--Sharon