topic Re: Nested Aggregation Not allowed? in QlikView

Nested Aggregation Not allowed?

Wed, 25 Nov 2020 16:16:04 GMT

HI,

This is the formula I want to use in a pivot table.

count( if(

(Status_Incidents='Closed' or Status_Incidents='Resolved'

or (Status_Incidents='Waiting' and [Assignment Status]<>'Completed')),Status_Incidents,

if((if((count(if([Assignment Status]<>'Forwarded', [Incident #])))=0,'Forwarded',

if((count(if(([Assignment Status]='Waiting' or [Assignment Status]='Accepted'), [Incident #])))=0,'Completed',

if((count(if([Assignment Status]='Accepted', [Incident #])))>0,'Accepted','Waiting')

)

))='Completed', InCompleted',Status_Incidents)

))

It is showing the error "Nested Aggregation Not allowed".

I dont think if I can use Aggr because of the conditions used.

Can anyone help me on this?

Re: Nested Aggregation Not allowed?

sunny_talwar — Tue, 27 Dec 2016 12:25:51 GMT

I think you are doing Count(if(Count(.... which is not possible without an Aggr() function... Why do you think that you cannot use Aggr() here? Can you may be share a sample for us to help you better?

Re: Nested Aggregation Not allowed?

oknotsen — Tue, 27 Dec 2016 12:26:53 GMT

I can not judge about your actual question, but looking at your nested IF circus, I would suggest to try and move a bunch of these things to your script and add them there as Flag fields. Bit better for performance to. That would deduce the size of your expression here which in turn might make it easier to find the problem.

Re: Nested Aggregation Not allowed?

Tue, 27 Dec 2016 12:30:39 GMT

I think aggr works like Group By. How do I use aggr function for lets say the following?

(count(if([Assignment Status]<>'Forwarded', [Incident #])))=0

Re: Nested Aggregation Not allowed?

Tue, 27 Dec 2016 12:31:50 GMT

Lets say even If I put the following in Script also:

(if([Assignment Status]<>'Forwarded', [Incident #]))

Again, we will have to use count within a count.

That is what is actually causing the issue.

Re: Nested Aggregation Not allowed?

sunny_talwar — Tue, 27 Dec 2016 12:48:14 GMT

This particular expression only includes 1 aggregation function -> Count....

Can you maybe share some more information as to what you are hoping/trying to do?

Re: Nested Aggregation Not allowed?

Tue, 27 Dec 2016 12:51:55 GMT

Yes One aggregation function is used. But Its like one inside another.

I am trying to get the count of Incidents according to some of the conditions which also include count.

Re: Nested Aggregation Not allowed?

sunny_talwar — Tue, 27 Dec 2016 12:56:17 GMT

Chetna -

I am afraid, I still don't understand your context here. Either share more information (may be like a sample or sample of data or images) or I would hope that someone else might be able to offer you better help.

Best,

Sunny

Re: Nested Aggregation Not allowed?

Wed, 28 Dec 2016 06:37:49 GMT

HI,

I want this report in QlikSense. where the formula for count is what I have mentioned above.

Re: Nested Aggregation Not allowed?

vinieme12 — Wed, 28 Dec 2016 07:36:10 GMT

Can you explain the calculation criteria's may be we can simplify

Not sure what you are trying to do here

count( if(

(Status_Incidents='Closed' or Status_Incidents='Resolved'

or (Status_Incidents='Waiting' and [Assignment Status]<>'Completed')),Status_Incidents,

if((if((count(if([Assignment Status]<>'Forwarded', [Incident #])))=0,'Forwarded',

if((count(if(([Assignment Status]='Waiting' or [Assignment Status]='Accepted'), [Incident #])))=0,'Completed',

if((count(if([Assignment Status]='Accepted', [Incident #])))>0,'Accepted','Waiting') ' Here you classify Accepted or Waiting but in the outer if you are considering both , why classify then?

)

))='Completed', InCompleted',Status_Incidents)

))

Can you post sample data /app and expected output

Re: Nested Aggregation Not allowed?

Wed, 28 Dec 2016 07:57:49 GMT

This is the criteria from Excel

=IF(OR(M85="Closed",M85="Resolved",AND(M85="Waiting",BC85<>"Completed")),M85,IF(BC85="Completed","TEK Completed",M85))

where

M85=Status

BC85==IF(BA85=0,'Forwarded',IF(BB85=0,'Completed',IF(AY85>0,'Accepted','Waiting')))

BA85==COUNTIFS(ASSIGNMENTS!$A$2:$A$5000,B85,ASSIGNMENTS!$C$2:$C$5000,"<>Forwarded")

BB85==AX85+AY85

AY85==COUNTIFS(ASSIGNMENTS!$A$2:$A$10000,B85,ASSIGNMENTS!$C$2:$C$10000,"Accepted")

AX85==COUNTIFS(ASSIGNMENTS!$A$2:$A$10000,B85,ASSIGNMENTS!$C$2:$C$10000,"Waiting")

B85=Incident #

Re: Nested Aggregation Not allowed?

vinieme12 — Wed, 28 Dec 2016 15:47:47 GMT

can you share a sample in excel?

also what is this count for , Resolved?Waiting?/Active?