topic Re: FirstSortedValue to find the last value. in QlikView

FirstSortedValue to find the last value.

Thu, 15 Dec 2016 14:00:05 GMT

I am using this expression to identify "the name of the State" that has "Maximum Contstituency":

=FirstSortedValue(State,(aggr(rank(count([Constituency-code])),[State-code])))

Now, when I want the reverse to find the name of the state that has "minimum Constituency", I use '-ve' sign:

=FirstSortedValue(State,-(aggr(rank(count([Constituency-code])),[State-code])))

It does not return any value. I am not sure where I am doing wrong with FirstSortedValue?

Re: FirstSortedValue to find the last value.

Anonymous — Thu, 15 Dec 2016 14:07:48 GMT

Hi, you can try with =FirstSortedValue(State,(aggr(rank(-count([Constituency-code])),[State-code])))

Re: FirstSortedValue to find the last value.

Thu, 15 Dec 2016 14:11:59 GMT

Hi,

I implemented what you mentioned but it too did not return any value.

Re: FirstSortedValue to find the last value.

Anonymous — Thu, 15 Dec 2016 14:27:07 GMT

This?

=FirstSortedValue(State,(aggr(rank(count([Constituency-code])),[State-code])), count(distinct State))

Re: FirstSortedValue to find the last value.

vishsaggi — Thu, 15 Dec 2016 14:43:34 GMT

Try ?

= FirstSortedValue(State, -Aggr(only(Count([Constituency-code])),[State-code], State))

Re: FirstSortedValue to find the last value.

sunny_talwar — Thu, 15 Dec 2016 14:48:11 GMT

Are there more than one state where the Count([Constituency-code]) is the same? If that's true which State do you want? Randomly any or Concatenate the States to show all?

For Randomly one, you can try this:

- gives Max and +gives min

MaxCount

=FirstSortedValue(DISTINCT State, -Aggr(Count([Constituency-code]), [State-code], State))

MinCount

=FirstSortedValue(DISTINCT State, Aggr(Count([Constituency-code]), [State-code], State))