Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Following one of Sunny’s solutions to https://community.qlik.com/message/1373145#1373145
Where I was 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)
And using one of the suggested solution:
=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])))
I am now also trying to count how many students are there that are shown,
=count(distinct <the above>)
so using:
=count(distinct 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]))))
May be this
=Count(DISTINCT Aggr(If(Max(TOTAL <Name> Aggr(If([Test Date] >= Max(TOTAL <Name> [Test Date], 3), Score), Name, [Test Date])) < 5, Name), Name, [Test Date]))
S1 and S3 I meant
This is the correct expression I think:
=Count(DISTINCT Aggr(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]))), [Test Date], [Name]))
thanks, but giving 3 I would have expect 2 (S1 & S3)
May be this
=Count(DISTINCT Aggr(If(Max(TOTAL <Name> Aggr(If([Test Date] >= Max(TOTAL <Name> [Test Date], 3), Score), Name, [Test Date])) < 5, Name), Name, [Test Date]))
Thank you, worked