I have a survey that respondents should complete every 3 months as they go through a year long programme (though they don’t always complete it). In this survey is a health measure that comes out with an average that indicates how ‘healthy’ they are, within certain parameters. Each individual also has a unique ID number that they put in at the start of the survey.
I’m looking for a way to calculate for a dashboard the average change in ‘health’ over time per individual. Once I’ve got the average I’d ideally like to just be able to pop it into a Number Chart or a Gauge Chart.
As far as I can work out, there are a few steps to it (not sure if this is the only way but this is how it makes sense to me - I welcome other suggestions!):
- Identify responses with the same ID number
- Identify the earliest and latest of these (this won’t always be within the last calendar quarter as sometimes respondents skip a survey)
- Calculate the change in ‘health’ between the earliest and latest responses (either as a number or as a percentage)
- Calculate the average of all the changes
Steps 1 & 2 are the ones I’m most stuck on - I think if those are figured out then I could do 3 & 4.
Any help would be greatly appreciated :)
