Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andyquirin
Partner - Contributor III
Partner - Contributor III

Fixing a Nested Aggregation

Hi Qlik Community,

I'm working on a piece of code for a KPI to determine '% of Items > Average Failure Date'.

In the data set, we have a list of items, which have a created date, failure date (only populated if the asset has failed), and age (difference between todays date and created date if there isn't a failed date, otherwise the difference between created and failed dates) and status ('Failure' if they have a failure date populated, otherwise 'Live').

My code is:

count(if(Item_status<>'Failure' and Item_Age > AVG({ <Item_status = {"Failure"}>} [Item_Age]) ,Item_ID))

/

Count(if(Item_status<>'Failure' ,Item_ID))

I'm getting the error: 'Error in Expression, Nested Aggregation is not allowed'.

The idea is in the numerator, I'm counting all assets which haven't failed whose age is beyond the average age of all failed items.

Then in the denominator, dividing it by the count of all non-failed assets.

Any idea how I can change it to bypass the nested error?

Thanks!!

2 Replies
Gysbert_Wassenaar

Perhaps by calculating the average outside the chart context:

count(if(Item_status<>'Failure' and Item_Age > $(=AVG({ <Item_status = {"Failure"}>} [Item_Age])),Item_ID))

/

Count(if(Item_status<>'Failure' ,Item_ID))


talk is cheap, supply exceeds demand
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I believe you need to provide an aggr for the avg:

aggr(AVG({ <Item_status = {"Failure"}>} [Item_Age]) , Item_ID)


-Rob