Would love some help with something from the forum! I feel like this is something I should be able to do but am stuck!
I have a table in a base that simply put a user enters a score from 1-5 (dragged in from a survey). The table can have multiple users and each user may enter that score multiple times eg once per week for a year.
What I am actually trying to do is track a users sentiment toward something over time.
So it basically the table looks like this:
UserID : Date of Entry : Score
User 1 : Monday : 2
User 2 : Monday : 4
User 1 : Tuesday : 3
User 2 : Tuesday : 3
User 1 : Wednesday : 4
User 2 : Wednesday : 1
User 1 : Thursday : 5
User 2 : Thursday : 1
Thats all fine but what I want to do is do is add a third column to the calc that shows a rolling average for each user sentiment over time. So in the above example User1 sentiment goes 2,3,4,5. If 5 is good and 1 is bad that means their sentiment is improving over time.
So the calc I would like to do for each user is to say sum(last 3 sentiment records)/3 to give an average, so I can give a general indication of the moving average.
Is this something I can do in airtable, it feels like it should be easy but just can figure out how!
Please help - and sorry if it is a really dumb question!