topic Set Analysis over function result in QlikView

Set Analysis over function result

Sat, 07 Jan 2012 23:08:09 GMT

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

Set Analysis over function result

Sun, 08 Jan 2012 00:07:33 GMT

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

Set Analysis over function result

orital81 — Sun, 08 Jan 2012 09:40:16 GMT

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"}

Set Analysis over function result

Sun, 08 Jan 2012 13:11:05 GMT

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

Set Analysis over function result

swuehl — Sun, 08 Jan 2012 13:32:14 GMT

try something like

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

Regards,

Stefan

Set Analysis over function result

Sun, 08 Jan 2012 13:55:41 GMT

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.

Set Analysis over function result

swuehl — Sun, 08 Jan 2012 14:09:05 GMT

Maybe like

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

Set Analysis over function result

Sun, 08 Jan 2012 14:21:50 GMT

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

Re: Set Analysis over function result

swuehl — Sun, 08 Jan 2012 14:34:34 GMT

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

Set Analysis over function result

Sun, 08 Jan 2012 14:46:05 GMT

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

I think it works now!

Thank you very much!