Fixing a Nested Aggregation

andyquirin — Tue, 08 Mar 2016 15:44:24 GMT

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!!

Re: Fixing a Nested Aggregation

Gysbert_Wassenaar — Thu, 10 Mar 2016 13:15:08 GMT

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

Re: Fixing a Nested Aggregation

rwunderlich — Thu, 10 Mar 2016 16:45:18 GMT

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

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

-Rob

topic Fixing a Nested Aggregation in QlikView

Fixing a Nested Aggregation

Re: Fixing a Nested Aggregation

Re: Fixing a Nested Aggregation