Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need help working out the best route for the following scenario.
I want the user to be able to pick two terms to compare a period. We then work out the progress for the selected terms. We have simple student data as below showing pupil scores per term for a subject.
Pupil | Term | Subject | Score |
Steve | T1 | Maths | 6 |
Steve | T2 | Maths | 7 |
Steve | T3 | Maths | 9 |
Steve | T4 | Maths | 14 |
Jim | T1 | Maths | 5 |
Jim | T2 | Maths | 8 |
Jim | T3 | Maths | 8 |
Jim | T4 | Maths | 13 |
Based on the progress we then want to summarise it as follows:-
Progress | ||
Start | End | Acheivement |
0 | 2 | OK |
3 | 4 | Great |
5 | Super |
So if a user picks Start Term 1, End Term 2 I would expect to see. This been the T2 less the T1 scores then the value rated against the lookup table.
Pupil | Subject | Score | Progress |
Steve | Math | 1 | OK |
Jim | Math | 3 | Great |
I've attached the data above into the app attached.
Any help would be great.
Steve
Maybe like this?
Pupil | Subject | Only({<Term = {T2}>}Score) - Only({<Term = {T1}>}Score) | Only( Aggr( If( Only({<Term = {T2}>}Score) - Only({<Term = {T1}>}Score) >= Start And Only({<Term = {T2}>}Score) - Only({<Term = {T1}>}Score) <= End, Acheivement) ,Pupil, Subject, Acheivement)) |
---|---|---|---|
Jim | Maths | 3 | Great |
Steve | Maths | 1 | OK |