topic Re: Nested aggregation not allowed in QlikView

Nested aggregation not allowed

arash64a — Sat, 24 Jun 2023 15:11:25 GMT

Hi, i use this code to determine which item finished in valid duration:
=if(if(Only(itemNo) and Only(finishDate), finishDate-startDate, Today()-startDate)<=validRequestDuration,1,0)

now i want to use this code to know how many item finished in valid duration:
=Count(if(if(Only(itemNo) and Only(finishDate), finishDate-startDate, Today()-startDate)<=validRequestDuration,1,0))

but its not work and show diffrent errors...

How can i get count of items that finish in valid duration?

Thanks in advanced

Re: Nested aggregation not allowed

QGTFS — Mon, 26 Jun 2023 07:58:24 GMT

You might want to use Sum instead of count, as it will currently count all occurrences regardless of being 1,0.

About the nested aggregation itself, you might want to look at set identifiers expressions in order to filter through and avoid this error, this will allow you select only the values you desire to then count them without using the if():

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Re: Nested aggregation not allowed

arash64a — Mon, 26 Jun 2023 15:50:02 GMT

Yes, its true.

can you write my script correctly with Sum please?

Re: Nested aggregation not allowed

BrunPierre — Mon, 26 Jun 2023 17:24:28 GMT

@arash64a Maybe something along these lines.

In script

Tmp:
LOAD itemNo,
startDate,
finishDate,
validRequestDuration

FROM xyz;

Table:
LOAD *,
If(Flag = 0, finishDate-startDate,
If(Flag = 1, Today())-startDate) as DateDiff;

LOAD itemNo,
Only(finishDate) as finishDate,
Only(finishDate) as startDate,
Only(validRequestDuration) as validRequestDuration,
If(Count(itemNo) > 1 and Count(finishDate) > 1, 1, 0) as Flag //1 for non distinct, 0 for distinct

Resident Tmp
Group By itemNo;

DROP Table Tmp;

Expression

=Sum(If(DateDiff <= validRequestDuration, 1 , 0))