above from last date only

[Anonymous]

following Re: set analysis on non-consecutive dates where Sunny kindly helped me out I still have a question...

I am now trying to figure out how many students (and who are they) that are still having  a grade below 5 for  THE LAST 3 consecutive test date (not just 3 consecutive test date in the past)

(I am using Q11)

so in the below I would have hope to see as a result: S1 and S3

Test Date	Name	Score
30/05/2017	S1	4
31/05/2017	S1	4
01/10/2017	S1	1
30/05/2017	S3	4
31/05/2017	S3	3
01/10/2017	S3	4

from:

LOAD * INLINE [
Test Date, Name, Score
01/01/2017, S1, 4
01/01/2017, S2, 10
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, 1
03/05/2017, S3, 2
30/05/2017, S1, 4
30/05/2017, S2, 1
30/05/2017, S3, 4
31/05/2017, S1, 4
31/05/2017, S2, 1
31/05/2017, S3, 3
01/10/2017, S1, 1
01/10/2017, S2, 10
01/10/2017, S3, 4
];

Many thanks,

Raphael

[sunny_talwar]

So you only want to check the last three dates and nothing else?

[Anonymous]

Yes please

[sunny_talwar · Accepted solution]

May be this

=If(Max(TOTAL <Name> {<[Test Date] = {">=$(=Date(Max([Test Date], 3)))"}>} Score) < 5, Only({<[Test Date] = {">=$(=Date(Max([Test Date], 3)))"}>}Score))

[Anonymous]

Exactly this thank you again

[sunny_talwar]

One thing I wanted to ask.... does each of the name have the same Test Date or the last three dates can differ by name?

[Anonymous]

They can differ.

[sunny_talwar]

So, then the above solution will not work... let me get you an alternate solution which will work regardless... it just might not be the most efficient solution though

[Anonymous]

ok thank you

[sunny_talwar]

Try this

=If(Max(TOTAL <Name> Aggr(If([Test Date] >= Max(TOTAL <Name> [Test Date], 3), Score), Name, [Test Date])) < 5, Only(Aggr(If([Test Date] >= Max(TOTAL <Name> [Test Date], 3), Score), Name, [Test Date])))

One more thing, are you going to be making selections in Test Date and hoping to go back three days from the selected Test Date?