topic Re: Filter after Set Analysis in AGGR function in QlikView

Filter after Set Analysis in AGGR function

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Filter after Set Analysis in AGGR function

sunny_talwar — Wed, 28 Sep 2016 18:08:26 GMT

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?

Re: Filter after Set Analysis in AGGR function

Thu, 29 Sep 2016 01:32:18 GMT

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

Re: Filter after Set Analysis in AGGR function

nagarjuna_kotha — Thu, 29 Sep 2016 02:53:17 GMT

Can u share sample data along with output?

-Nagarjun

Re: Filter after Set Analysis in AGGR function

Thu, 29 Sep 2016 05:31:20 GMT

Please see sample app attached.

Thanks,

Yuvir

Re: Filter after Set Analysis in AGGR function

sunny_talwar — Thu, 29 Sep 2016 19:25:55 GMT

Is this the goal?

Dimension:

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

Expression:

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

Re: Filter after Set Analysis in AGGR function

Thu, 29 Sep 2016 20:54:01 GMT

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

sunny_talwar — Thu, 29 Sep 2016 21:01:49 GMT

How about this:

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

Re: Filter after Set Analysis in AGGR function

Thu, 29 Sep 2016 21:18:59 GMT

Works like a charm! thanks a million Sunny!

Re: Filter after Set Analysis in AGGR function

swuehl — Thu, 29 Sep 2016 21:20:26 GMT

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

Re: Filter after Set Analysis in AGGR function

Fri, 30 Sep 2016 06:29:31 GMT

Thanks guys. really appreciate the help, saved me a lot of time.