count giving Nested aggregation error

[Anonymous]

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

[Anonymous]

S1 and S3 I meant

[jonathandienst]

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

[Anonymous]

thanks, but giving 3 I would have expect 2 (S1 & S3)

[sunny_talwar · Accepted solution]

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

[Anonymous]

Thank you, worked