Ignore dimension inside set analysis but not outside problem

[#1]

I have a problem in QlikView.

If anyone can help me with this problem I would be very grateful

.

I have requests identified with a unique id. Each request is connected to several actions and each action is connected to a team and a status. I want to count the number of requests that are active, i.e. those whose latest action doesn't have a closed or archived status and I know that I can do that with this expression:

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

This works fine when no dimension is used but I want to be able to count the requests by team, and when I use team as a dimension, the expression counts the last actions of the requests associated with each team, instead of counting the last action of the requests ignoring the dimension, and then distributing them between the teams.

Is there any way to do this? I think I should have a Total to the firssortedvalue function but maybe I'm not using it right because that doesn't work.

Thanks in advance.

Best regards.

[swuehl]

What do you mean with "distibuting" then, if not counting "the last actions of the requests associated with each team"?

[#3]

Let me try to explain better.

I want to get the last action for each request, regardless of the team. I don't want the last action of teamA in request1, I want just the last action of request1 and so on.

But then I want to distribute those last actions for the teams connected to them.

I want to count all the requests last actions, and then see that result for each team.

It's not easy to explain this, I hope you understand what I'm trying to get.

[swuehl]

Maybe a concrete sample would help..

For now, I think you could achieve this with TOTAL qualifier, but since I am not sure what you are after, I can't detail the syntax.

[#5]

I attached the example you sent me yesterday.

I added a team for each action.

I want to be able to see the active (not closed or inwork in the last action) requests, by the team that was connected to the last action of that request.

[swuehl · Accepted solution]

Try this as expression:

=count({$<Request = {"= not match(FirstSortedValue(Status,-Action),'CLOSED','INWORK') "}, Action = {"$(='('&concat(aggr(max(Action),Request),'|')&')')"} >}  Request)

or a bit more simple, since we don't need the FirstSortedValue() anymore (replaced by the selection of max(Action) per Request):

=count({$<Status -= {'CLOSED','INWORK'}, Action = {"$(='('&concat(aggr(max(Action),Request),'|')&')')"} >}  Request)

See also attached.

Regards,

Stefan

edit:

Even more simple:

=count({$<Status -= {'CLOSED','INWORK'}, Action ={$(=concat(aggr(max(Action),Request),','))} >}  Request)

[#7]

I'm still adapting this to my project which is a lot more complicated than this example, but I think it works!

Thank you very much! You have been of great help!