topic Re: FirstSortedValue, Aggr and Count with Set Analysis in QlikView

FirstSortedValue, Aggr and Count with Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am looking to get a value in a Text Object. I have created a Straight table with the correct numbers but am struggling to get a single number in a Text object.

The dimension on the table is:

=aggr(FirstSortedValue(location,-seq_no),orderID)

(This gives me the list of last locations for all orders)

The expression is:

=count(aggr(FirstSortedValue(location,-seq_no),orderID))

(This gives me the number of order in last location)

Each order has several locations. I am looking for the count of an order in a certain locations it was last in.

I am looking to get a Text Object telling me the number of orders that were last in 1 particular location.

When I try the following I get a count of all orders that have passed thought the location. Not only the ones that have that location as last in their sequence.

=count({< location = {'Paris'}>} aggr(FirstSortedValue(location,-seq_no),orderID))

Could anybody help here?

Re: FirstSortedValue, Aggr and Count with Set Analysis

sunny_talwar — Thu, 24 Mar 2016 14:52:33 GMT

Can you check if this makes any difference:

=Count({<location = {'Paris'}>} aggr(FirstSortedValue({<location = {'Paris'}>} location,-seq_no),orderID))

Re: FirstSortedValue, Aggr and Count with Set Analysis

Anonymous — Thu, 24 Mar 2016 14:57:27 GMT

That does not make a difference.

Re: FirstSortedValue, Aggr and Count with Set Analysis

swuehl — Thu, 24 Mar 2016 14:57:34 GMT

You can also try

=Count{<orderID = {"=FirstSortedValue(location,-seq_no)='Paris'"}>} DISTINCT orderID)

Besides this, I think your expression in your first table could just be

=Count(DISTINCT orderID)

edit: missed a closing bracket

Re: FirstSortedValue, Aggr and Count with Set Analysis

Anonymous — Thu, 24 Mar 2016 15:10:32 GMT

That did the trick.

Thanks for simplifying the first table as well.

The use of double quotes is new to me in the set analysis.

Thanks!!