Filter after Set Analysis in AGGR function

[#1]

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

[sunny_talwar]

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?

[#3]

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):

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

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

[nagarjuna_kotha]

Can u share sample data along with output?

-Nagarjun

[#5]

Please see sample app attached.

Thanks,

Yuvir

[sunny_talwar]

Is this the goal?

Dimension:

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

Expression:

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

[#7]

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

[sunny_talwar · Accepted solution]

How about this:

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

[#9]

Works like a charm! thanks a million Sunny!

[swuehl]

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