Calculating Average Score of Only Last Items per Each User in a Team Dashboard

[admir]

Hey guys, to simplify it, in my app I am collecting a “Score” for each user every week.

Users are grouped into different teams in the database.

Right now, I am trying to create a dashboard for the teams, which would show the average “Score” per team and how it progresses over time.

I tried this, and it works to give me an average of the score, but it is giving me the average of every record. What I want to achieve is that it gives me the average of the :lastitem’s score, for each member.

So in the example “admir” has multiple records, and “samar” has only 1 so far. In the team dashboard I want to show the average of the last record by “admir” and last record by “samar”.

Anyone has any idea how I could achieve that? Let me know if it is unclear and any help would be really appreciated!

[shpak.serhiy]

Sometimes it is just not possible to get the data via single filtering expression (even complex one).
In your case what I would do:

1. create a field on the user data type called “Last Score”.
2. implement a backend workflow that will update this field of every user with last Score item of this user. The triggering event depends on your app.
3. having the Last score field on every user you can build something like: Parent group’s Team’s Member’s:each item’s Last Score: average

[admir]

Thanks for the suggestion @shpak.serhiy!

I think I can maybe set it up like this, but one thing might become an issue.

Doing it this way, wouldn’t it make it impossible to track the average over time? So showing in a graph how the team’s Score has changed from week to week.

[shpak.serhiy]

The approach I described adds a new field and updates it with redundant data to make it possible to calculate the latest averages.

So nothing is deleted and you can use the data for showing the historical averages.