Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## above from last date only

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:

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

1 Solution

Accepted Solutions
MVP

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

18 Replies
MVP

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

Anonymous
Not applicable
Author

MVP

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
Not applicable
Author

Exactly this thank you again

MVP

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
Not applicable
Author

They can differ.

MVP

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
Not applicable
Author

ok thank you

MVP

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?

Community Browser