Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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))
I believe you need to provide an aggr for the avg:
aggr(AVG({ <Item_status = {"Failure"}>} [Item_Age]) , Item_ID)
-Rob