I am trying to calculate the Exponential Moving Average (EMA) for a graph, but this requires already having the prior period’s EMA.
I can point to the Prior EMA field, but since it is a calculated field, Glide freaks out and says:
I even tried to obscure the field by first creating a TEMPLATE, and then using a math field to convert the template back to a number, but still get the error.
The bottom line is that “K” is a constant, in my case 0.2500, and “C” is also a calculated field (partly obscured because it is an ITE field) and is the 7-year moving average.
I think this is something I will not be able to resolve with Glide’s native columns. I used to use Excel to calculate things (which for some reason does not have a problem with ‘dependencies’), but this is a free team.
“P” is the field giving me the problem.
EMA Formula
The formula for calculating EMA is:
EMA = (K x (C – P)) + P
Where:
C = Current Price
P = Previous Period’s EMA (for the first period calculated the SMA is used)
K = Exponential Smoothing Constant (this applies appropriate weight to the most recent security price, using the number of periods specified in the moving average. The most common smoothing constant is 2, but the higher it is the more influence recent data points have on the EMA)
How are you getting the previous period’s EMA? I assume through a relation and lookup?
Yes using the old trick about numbering the rows and then using the row to subtract one and getting the row ID of the prior and creating a relation.
The only thing I can think of is to write the calculated EMA value into a non-computed column, and then use that in subsequent calculations.
That’d be easy if it was on a detail screen. But it needs to happen to the whole table at once to be useful for a graph.
Pretty sure these values have to live in a table somewhere even though they’re computed values. It wouldn’t make sense for Glide to be constantly generating them. Glide probably generates them once into some temporary table. It’s too bad that the math column can’t use the temporary table in some fashion behind the scenes.
I faced a similar issue. To solve this, as far as I remember I wrote the calculated value a on a non-computed column on the first row of database. Then made use of ITE, and Row Indexes to do my further calculations. Hope this helps.
Thanks but the calculated value is different for every single row. We’re taking a 7-year moving average and then on top of that we’re trying to get an exponential moving average. A moving average is used to spot trends.
So should every row be recalculated each time a new row is added?
If that’s the case, that seems a bit odd to me, because usually a moving average is based on past and current data, rather than future data. But quite possibly I’m misunderstanding.
1 Like
Can you show how your math column is ideally configured… even though glide won’t accept it. I just want to see how you are referencing the various columns.
No, but manually intervening every time a new row is added? In this case, it might be OK, but when I develop a solution, I like it to be able to be applied in other situations. This type of data is used for forecasting and is most often used when trading stocks or commodities.
Hi Jeff, well, I start out with the computed column for the seven year moving average, which formerly had been supplied by Excel, but now needs to be supplied by Glide. So I did that first.
Then I fill in Yesterday’s EMA through a LOOKUP. Since the first column,needs to be the seven year moving average, I filter it through a ITE Column:
K is the “Smoothing Factor”, and is calculated.
I then try to apply the formula for Today’s EMA:
Then I apply the replacements:
I’ll have to sit and think on this for a bit. Theoretically you do not have a circular dependancy because your lookup is always pulling from the previous row, but I think Glide only checks on a surface level and the fact that the result of your math column is ultimately used within the formula in that same column, regardless of where the input value comes from, it’s seen as a circular dependancy.
If your lookup ever accidentally changed to return the current row value instead of the prior row value, that could throw the whole thing into a wicked loop and that’s what Glide is trying to prevent by preventing you from doing that. I think Google and Excel check a little deeper and know you aren’t trying to do weird things so they allow it. In some cases they will allow circular dependancies but they cap the loops at a certain number if that ever happens and you enable the feature in the first place.
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.
Would you be willing to create a simplified template app with some sample data that I could play with? I think I have a plan in my head, but gotta work though in practice before I can explain it.
2 Likes
Hi Jeff, I just sent you a message. My math is simple? LOL, I hate math.
1 Like
Thanks. I’ll take a look tonight.
Why would you do that? (that isn’t what I suggested).
Anyway, looks like Jeff is on the case, so I’ll butt out
OK, I spent about 2 hours trying to figure out why my javascript was returning unexpected results, just to finally realize that I was passing the wrong column as a parameter. Can’t believe I missed that.
I ended up creating a new table so it would be easier for me to work it out and so I could refer back to your existing table without having everything mixed together.
I think I have it working as far as I can tell. Do you have numbers from an excel spreadsheet or something like that to compare against to check my work? I think the only thing at this point would be rounding. Let me know if something still doesn’t look right.
Unfortunately I only have the simple moving average and that is what I used to compare my first formula against. Also unfortunately I’m going to be out all day tomorrow but I will try to look at this tomorrow night.
Initially glancing at it everything looks cool. I haven’t dug in any further than your post at this point.
Thanks for having a look at this!
1 Like
No worries. Whenever you get time.
Also want to add that the first 6 years just redisplay the same temperature for that year. It’s the 7th year where it starts doing the average and uses the 6th year value as the prior year value. Hope that makes sense. Wasn’t sure if I handled that part correctly.
1 Like
Yes. Because there’s no moving average for the first years it looked funny on the graph with zeros so that’s why I had it to redisplay the same avg.
1 Like