topic Re: count giving Nested aggregation error in QlikView

count giving Nested aggregation error

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

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

Re: count giving Nested aggregation error

Anonymous — Fri, 03 Nov 2017 08:55:31 GMT

S1 and S3 I meant

Re: count giving Nested aggregation error

jonathandienst — Fri, 03 Nov 2017 09:22:40 GMT

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

Re: count giving Nested aggregation error

Anonymous — Fri, 03 Nov 2017 09:25:51 GMT

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

Re: count giving Nested aggregation error

sunny_talwar — Fri, 03 Nov 2017 11:48:25 GMT

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

Re: count giving Nested aggregation error

Anonymous — Fri, 03 Nov 2017 11:51:19 GMT

Thank you, worked