if avg aggr with average solutions time calculation

[kasimyc]

hi Qlikers,

i have a simple formul. calculate the average solution time. But i need to add one more criteria. I want to include the average of resolved records. I have specified the sample table and the current calculation below. If I want to calculate on HistoryStatu='Closed' here, how can I do it?

I would appreciate if you could guide me on how to solve this with set expression

Your help is appreciated

set exp: avg(aggr(max(HistoryTime)-min(HistoryTime),ProblemId)

[anthonyj · Accepted solution]

Hi @kasimyc ,

If you only want the min/max calculated on the ProblemId that have had a "Closed" status then you can use the possible ( P() ) function to return this list before calculating the min/max. This way in your example it should only return calculations for ProblemID = 2:

avg(aggr(max({$<ProblemId=p({1<HistoryStatu={Closed}>}ProblemId)>}HistoryTime)-min({$<ProblemId=p({1<HistoryStatu={Closed}>}ProblemId)>}HistoryTime),ProblemId)

Regards

Anthony

[kasimyc]

thanks Anthony, good trick