Weekly Averages Using Javascript

Monthly and Daily averages are easy to calculate with EDT built in functions (see Monthly and Daily Average Calculation for Sensor Data (Solved))

Weekly averages can be calculated by using the ISO8601 defined week within a year to delineate the periods of the week and a pivot operation can be used to calculate the average within each period. The following JS code allows week calculation in EDT (sourced from https://stackoverflow.com/questions/6117814/get-week-of-year-in-javascript-like-in-php) with string concatenation, Output [yyyy-weeknumwithleadingzero]:

function getweek(x){
 Date.prototype.getWeekNumber = function(){
   var d = new Date(Date.UTC(this.getFullYear(), this.getMonth(), this.getDate()));
   var dayNum = d.getUTCDay() || 7;
   d.setUTCDate(d.getUTCDate() + 4 - dayNum);
   var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1));
   return Math.ceil((((d - yearStart) / 86400000) + 1)/7)
 };
return (new Date(x).getWeekNumber());
}
function leftFillNum(num, targetLength) {
    return num.toString().padStart(targetLength, 0);
}
//calculate the current year
var year = (new Date($(Date)).getUTCFullYear());
var weeknum = getweek($(Date));
var paddedweeknum = leftFillNum(weeknum, 2);
var combinedreturn= (year + "-" + paddedweeknum);
return(combinedreturn);

This is assuming a date is provided via the column name/variable “$(Date)”

This approach uses nested functions available in the Javascript implementation in EDT: https://stackoverflow.com/questions/3212477/can-you-write-nested-functions-in-javascript

An alternative approach uses a slightly different output format but operates the same way, Output [yyyy,weeknumwithleadingzero]:

//Calculate ISO8601 Weeknumber, note there may be a 52nd/53rd week leading into the next year normally
function leftFillNum(num, targetLength) {
 return num.toString().padStart(targetLength, 0);
}
function getWeekNumber(d) {
// Copy date so don't modify original
d = new Date(Date.UTC(d.getFullYear(), d.getMonth(), d.getDate()));
// Set to nearest Thursday: current date + 4 - current day number
// Make Sunday's day number 7
d.setUTCDate(d.getUTCDate() + 4 - (d.getUTCDay()||7));
// Get first day of year
var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1));
// Calculate full weeks to nearest Thursday
var weekNo = Math.ceil(( ( (d - yearStart) / 86400000) + 1)/7);
// Return array of year and week number
return [d.getUTCFullYear(), leftFillNum(weekNo, 2)];
}
return (getWeekNumber (new Date($(Date))));

In practice:

If one were looking to average only weekdays, identifying these target days is done with another JS operation (code below) followed by a filter that selects based on the identification of weekdays versus weekends:

var is_weekend =  function(date1){
var dt = new Date(date1);
  if(dt.getDay() == 6 || dt.getDay() == 0)
     {
      //return "1";
	  return "Weekend";
      } else
      //return "0";
  	return "Weekday";
  }
 return (is_weekend(new Date($(Date))));
1 Like