Creating a growth measurement over time

Topic Labels: Data Interface Designer
249 2
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast
Hi there.
For a while I have been trying to figure out the following. Maybe you can help. 
For our participants we measure there progress over time. We do this by filling in a form about every 2 months. 
We have one base with all the participants (personal data).
Since one participant can follow multiple trajectories, we have one base with all the trajectories linked to the participants. 
The results of the form also appear in a seperate base. For everytime the form is filled in, a new row is created (which is linked to the right  trajectory)
My question is the following:
On 1-1-2023 John scores 6/10 on our communication scale.
On 1-3-2023 John scores 7/10 on our communication scale.
On 1-5-2023 John scores 8/10 on our communication scale.
On 1-7-2023 John scores 7/10 on our communication scale.
On 1-9-2023 John scores 9/10 on our communication scale.
On 1-1-2023 Cristia scores 4/10 on our communication scale.
On 1-3-2023 Cristia scores 4/10 on our communication scale.
On 1-5-2023 Cristia scores 5/10 on our communication scale.
On 1-7-2023 Cristia scores 6/10 on our communication scale.
On 1-9-2023 Cristia scores 6/10 on our communication scale.
And way more people...
I would like to create a dashboard where our stakeholders can see what progress is being made on a organisation level (so not per person, since they are not allowed to see all the personal data).
So which fields should I create to obtain the right data for a dashboard where a stakeholder can fill in:
Give me the average progress on Communication in the first half a year of 2023. 
The main fields are:
- Date where the form was filled in
- Name participants and trajectory
- Score communication (and other fields, but for the example one is enough)
(I've already worked with first and last measurements via a rollup, but this is not sufficient, since sometimes the first measurement will be outside of a timeframe which results in wrong data)
I look forward to hear from you!
2 Replies 2

Hmm, if you want to just have it in a dashboard in an Interface, you could just do it like this?

Screenshot 2024-03-25 at 8.30.49 PM.png
If you want it to be actual records in your base you'd need another table though, call it "Summaries" or something and link it to the table that gets the form submissions.  You could then create a formula field that would output whether it was the first or second half of the year as the text "First Half 2024", "Second Half 2024" etc, and then have an automation that'll paste the value from the formula field into the linked field, that way all the form submissions from the appropriate time periods will be linked to a single record in the Summaries table.  After that, you could do rollups to do averages and such

Community Manager
Community Manager

Here is an example that tracks vehicles over time.  This could easily be your students scores each month.