topic Re: above from last date only in QlikView

above from last date only

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: above from last date only

sunny_talwar — Wed, 01 Nov 2017 11:56:13 GMT

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

Re: above from last date only

Anonymous — Wed, 01 Nov 2017 11:56:58 GMT

Yes please

Re: above from last date only

sunny_talwar — Wed, 01 Nov 2017 12:06:51 GMT

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

Re: above from last date only

Anonymous — Wed, 01 Nov 2017 12:09:46 GMT

Exactly this thank you again

Re: above from last date only

sunny_talwar — Wed, 01 Nov 2017 12:10:44 GMT

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?

Re: above from last date only

Anonymous — Wed, 01 Nov 2017 12:12:26 GMT

They can differ.

Re: above from last date only

sunny_talwar — Wed, 01 Nov 2017 12:18:40 GMT

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

Re: above from last date only

Anonymous — Wed, 01 Nov 2017 12:19:56 GMT

ok thank you

Re: above from last date only

sunny_talwar — Wed, 01 Nov 2017 12:21:57 GMT

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?

Re: above from last date only

Anonymous — Wed, 01 Nov 2017 12:26:42 GMT

thank you, no sure I follow your question. can you explain again, sorry.

Re: above from last date only

sunny_talwar — Wed, 01 Nov 2017 12:39:27 GMT

In the example above, you would want to always see it for 01/10/2017, 31/05/2017, 30/05/2017 or do you expect to select 31/05/2017 and then compare 31/05/2017, 30/05/2017, 17/03/2017?

Re: above from last date only

Anonymous — Wed, 01 Nov 2017 12:42:39 GMT

Idealy yes, but guess that it is not really doable?

Re: above from last date only

sunny_talwar — Wed, 01 Nov 2017 12:49:50 GMT

It is possible, but will have to be coded in... do you want me to code it or would you like to give it a try?

Re: above from last date only

Anonymous — Wed, 01 Nov 2017 12:51:54 GMT

Honestly I right now would not be sure where to start from, so if you could code it , it would be great! thanks

Re: above from last date only

sunny_talwar — Wed, 01 Nov 2017 12:59:16 GMT

Try this

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

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

Re: above from last date only

Anonymous — Wed, 01 Nov 2017 13:01:33 GMT

I am speechless ! thank you

Re: above from last date only

sunny_talwar — Wed, 01 Nov 2017 13:10:23 GMT

No problem, please take some time to mark any helpful responses

Best,

Sunny

Re: above from last date only

Anonymous — Wed, 01 Nov 2017 13:12:04 GMT

will do, I was not aware of this option, many thanks