Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Set Analysis over function result

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Set Analysis over function result

Maybe like

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

View solution in original post

9 Replies
Highlighted
Not applicable

Set Analysis over function result

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

Highlighted
Partner
Partner

Set Analysis over function result

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

Highlighted
Not applicable

Set Analysis over function result

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

Highlighted
MVP
MVP

Set Analysis over function result

try something like

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

Regards,

Stefan

Highlighted
Not applicable

Set Analysis over function result

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.

Highlighted
MVP
MVP

Set Analysis over function result

Maybe like

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

View solution in original post

Highlighted
Not applicable

Set Analysis over function result

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

Highlighted
MVP
MVP

Re: Set Analysis over function result

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

Highlighted
Not applicable

Set Analysis over function result

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


I think it works now!

Thank you very much!