Exponential Moving Average

[ThinhDinh]

So what happens here is using JS in this manner would be the solution?

@Jeff_Hager

I think the solution here probably lies with a javascript column, where you query all of the prior rows into the current row, pass all of the data into a javascript column, and then have it loop though and do the math. I’m pretty sure it’s doable…it’s just a matter of eliminating the circular dependancy and doing it a different way. The result from the javascript would never be passed into the same javascript. It’s just doing the rolling average on the fly for each row based on all the previous row data. So row 2 would grab all the necessary data from row 1 and do the math in a single loop in a javascript column. Row 10 would grab all the data from rows 1 through 9 and do the math in 9 or 10 loops in the javascript column. I don’t know how many rows you would ultimately have, but each row would run an additional loop so the last row would take a bit longer to run compared to the first row, but your math is pretty simple so hopefully it be pretty efficient.

[Jeff_Hager]

Yeah, that’s what I ended up doing. Basically @David_Gabler wanted to use the result of a math column from the previous row in the same math column in the current row, and do that for about 150 rows. Since the math column was ultimately referring to itself, Glide saw it as a circular dependancy.

If you look at the image I posted previously, I first created a query that would get the prior 6 rows and build a single pipe delimited list. Then I created a second query to get All previous rows and create a double pipe delimited list of those single pipe delimited lists. Then the javascript splits the double pipe into an array, loops through the array, calculates a simple average of each single piped array, performs the math from the formula, and sets a result variable. On the next loop, the math uses the previously set result in the math for the next result. It just keeps looping and updating the result until it’s processed through the array. So really I’m just getting all the numbers I need from all prior and current rows into a single text string and passing that into the javascript.

Each row in the table ends up performing one more loop than the previous row, so by the 150th row, it’s doing 150 loops.

Here is code for anyone interested.

function movingAverage(allData) {
  let allPrior = allData.split('||');
  let sF = 0;
  let result=0;

  //Smoothing Factor (0.25)
  sF = (2/(7+1));

  // Loop through all prior year groups of 7 year averages to calculate moving average to use for the next group.
  for (let value of allPrior) {
    let arrTemps = [];
    let avgTemps = 0;

    // Split Temps into number array
    arrTemps= value.split('|').map(Number);  

    // Calculate Average from array
    avgTemps=arrTemps.reduce((acc, val) => acc + val, 0)/arrTemps.length;

    // Check if the array has less than 7 items
    if (arrTemps.length < 7) {
      // Return the last item in the array
      avgTemps = arrTemps[arrTemps.length-1];
      result = avgTemps;
    }

    result = (sF * (avgTemps - result)) + result
    result = Math.round(result*100)/100

  }
return result.toFixed(2);

}



// Moving average
return movingAverage(p1);

[ThinhDinh]

Thanks for sharing!