Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Load or InterRecord with Aggr?

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

1 Reply
MVP
MVP

Re: Load or InterRecord with Aggr?

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))
JimMaths3Great
SteveMaths1OK