cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

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

RM12
13-Aquamarine

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
yhan
17-Peridot
(To:RM12)

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

View solution in original post

4 REPLIES 4
yhan
17-Peridot
(To:RM12)

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
13-Aquamarine
(To:yhan)

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
17-Peridot
(To:RM12)

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
23-Emerald II
(To:RM12)

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

Announcements