set analysis on non-consecutive dates

[Anonymous]

LOAD * INLINE [
Test Date, Name, Score
01/01/2017, S1, 9
01/01/2017, S2, 5
01/01/2017, S3, 6
17/03/2017, S1, 9
17/03/2017, S2, 7
17/03/2017, S3, 7
03/05/2017, S1, 4
03/05/2017, S2, 10
03/05/2017, S3, 2
30/05/2017, S1, 8
30/05/2017, S2, 5
30/05/2017, S3, 8
31/05/2017, S1, 5
31/05/2017, S2, 5
31/05/2017, S3, 10
01/10/2017, S1, 5
01/10/2017, S2, 9
01/10/2017, S3, 9
];

I am trying to figure out how many students manage to keep a consecutive score  between 5 and 10 for 3 consecutive test date, to show that in both a text object , and also in a straight table.

Any idea?

Thank you

[sunny_talwar]

Try this

=Count(DISTINCT Aggr(If(RangeMin(Above(Score, 0, 3)) >= 5 and RangeMax(Above(Score, 0, 3)) <= 10 and RowNo() > 2, Name), Name, ([Test Date], (NUMERIC))))

[Anonymous]

I got feeling right but maybe a typo somewhere  "Error in expression: ')' expected"?

[sunny_talwar]

Are you using QV12 or above? If not, then it might not work for you because sorting in Aggr() function was made available after QV12 was launched. If you don't have QV12, you will have to make sure that Test Date is sorted in ascending order in script

[sunny_talwar · Accepted solution]

The expression though shows an error, gives the output like this

[Anonymous]

as always you are right I am using 11, many thanks

[sunny_talwar]

Did it work then by removing the sorting from Aggr?

[Anonymous]

nor sure which part I need to remove, if you can give me the expression .

[sunny_talwar]

This

=Count(DISTINCT Aggr(If(RangeMin(Above(Score, 0, 3)) >= 5 and RangeMax(Above(Score, 0, 3)) <= 10 and RowNo() > 2, Name), Name, [Test Date]))

But make sure that Test Date is sorted in ascending order for this expression to work

[Anonymous]

Working perfectly, many thanks