Set Analysis over function result

[#1]

I have the following scenario in qlikview, in which I need some help.

There are requests identified with a unique id. Each ticket is connected to several actions and each action is connected to a team and a status.

I want to count the number of requests whose latest action doesn't have a closed status. Any idea how I can do this?

I tried to get the latest action using max() but I need to restrict those actions to unclosed ones using set analysis outside max, and I don't have any idea how...

Thank you in advance.

Best regards

[#2]

hi,

i think you can use set analysis, like

count(({$<Action fields ={"$(#=max(Action fields))"},Status Fieleds <>{closed}>} requests)

this set analysis will count the request and will fixe the max of action with status enclose

[orital81]

Following belgacem.ejmal reply,

If it doesn't work I would suggest to replace <> with -=

Instead of : Status Fieleds <>{closed}

Use this      : Status Fieleds  -= {closed}

You can also use this :

Status Fieleds = {"<>closed"}

[#4]

Thank you very much for the replies. I think that is the right way to go, but if I use max(Action fields) I will only get the latest action for all requests, and I need the latest action for each request. Any idea on how to do that? I think maybe aggr can help with that but I don't know how to combine it with that expression...

[swuehl]

try something like

=count({$<RequestID = {"=FirstSortedValue(Status,-ActionID) <> 'CLOSED' "} >} distinct RequestID)

Regards,

Stefan

[#6]

Thanks for the reply.

I think that does it. I just have one question more. If there are status other than closed that I don't want to count, how can I add that to this expression. I usually use =-{'closed', 'arquived'}, but with <> I don't know how to use multiple values.

[swuehl · Accepted solution]

Maybe like

=count({$<RequestID = {"= not match(FirstSortedValue(Status,-ActionID),'closed','arquived') "} >} distinct RequestID)

[#8]

Thanks for the reply, but that doesn't work. The result becomes zero.

[swuehl]

Please check the spelling of all field names etc. Above expression works at my place, please see attached.

[#10]

Yeah you're right. There was some error on my adaption.

I think it works now!

Thank you very much!