Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Filter after Set Analysis in AGGR function

Hi Qlikview Experts,

I have a straight table with the calculated dimension of:

=Aggr(FirstSortedValue({<LogType={2}>}[process status],-communicationQueueLogId),CommunicationQueueId)

And an expression of:

COUNT({<LogType={2}>}DISTINCT CommunicationQueueId)

This straight table works as expected with the correct results. I would like to add a filter after that expression has been calculated.

So, the calculated dimension and expression must evaluate to a certain result, and then after that has been evaluated, apply a certain filter.I have tried the obvious things like adding that condition in my set analysis or as a filter but the results are incorrect.

Is this possible? I want to add where [process status]='Done'

Thanks,

Yuvir

1 Solution

Accepted Solutions

Re: Filter after Set Analysis in AGGR function

How about this:

If(Aggr(FirstSortedValue({<LogType={2}>}[process status],-communicationQueueLogId),CommunicationQueueId) = 'Done', Count({<LogType={2}, [process status] = {'Done'}>}DISTINCT $(ident)))

Capture.PNG

10 Replies

Re: Filter after Set Analysis in AGGR function

Not sure I understand what you are looking for? You want that first the expression evaluates to something and then apply another filter? Can you may be elaborate a little?

Not applicable

Re: Filter after Set Analysis in AGGR function

Hi Sunny,

The use case is as follows:

I am using the AGGR function together with the FirstSortedValue function to determine the latest status of an application.This works perfectly. What i need to further break this down into is a count of applications where the latest status is 'Done'.

So what Qlikview is doing currently, is looking for the latest 'Done' status on an application rather than finding all the applications latest status first and then finding only the ones where the latest is 'Done'.

This is the value i get when i do not specify the 'Done' status in the set analysis(The correct value):

Capture.JPG

This is the value i get when i specify the 'Done' status in the set analysis(The wrong value)

Capture2.JPG

This is my calculated dimension:

=Aggr(FirstSortedValue({<LogType={2},[process status]={'Done'}>}[process status],-communicationQueueLogId),CommunicationQueueId)

This is my Expression:

COUNT({<LogType={2}>}DISTINCT CommunicationQueueId)

Thanks,

Yuvir

Partner
Partner

Re: Filter after Set Analysis in AGGR function

Can u share sample data along with output?

-Nagarjun

Not applicable

Re: Filter after Set Analysis in AGGR function

Please see sample app attached.

Thanks,

Yuvir

Re: Filter after Set Analysis in AGGR function

Is this the goal?

Capture.PNG

Dimension:

=Aggr(FirstSortedValue({<LogType={2}>}[process status],-communicationQueueLogId),CommunicationQueueId)

Expression:

Count({<LogType={2}, [process status] = {'Done'}>}DISTINCT $(ident))

Not applicable

Re: Filter after Set Analysis in AGGR function

Hi Sunny,

I have tried the solution above, this does not give the desired results.

The desired results should be 'Done' 13818 only, all the other statuses must be ignored.

It is returning the other values because those records were previously in a 'Done' status but have now progressed beyond that. I only want what is Done currently i.e 13818.

Thanks,

Yuvir

Re: Filter after Set Analysis in AGGR function

How about this:

If(Aggr(FirstSortedValue({<LogType={2}>}[process status],-communicationQueueLogId),CommunicationQueueId) = 'Done', Count({<LogType={2}, [process status] = {'Done'}>}DISTINCT $(ident)))

Capture.PNG

Not applicable

Re: Filter after Set Analysis in AGGR function

Works like a charm! thanks a million Sunny!

MVP
MVP

Re: Filter after Set Analysis in AGGR function

Or maybe add the condition to the calculated dimension and use a simplified expression:

=Aggr(If(FirstSortedValue({<LogType={2}>}[process status],-communicationQueueLogId)= 'Done','Done'),CommunicationQueueId)

=Count(DISTINCT $(ident))

edit:

or just use a text box

='Done: ' &

Sum(Aggr(If(FirstSortedValue({<LogType={2}>}[process status],-communicationQueueLogId)= 'Done',1),CommunicationQueueId))