Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 

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?


2 Replies

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

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

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