topic Re: Find largest dimension within a dimension in QlikView

Find largest dimension within a dimension

Fri, 28 Feb 2014 09:08:58 GMT

Hi all,

This is my first post so I'll try to keep it as detailed as possible. What I'm trying to do is to find the largest dimension within a dimension.
Let me try to explain.

I have a list of locations with different categories of people.

Locations	Category	Number of people
A	X	532
A	Y	2346
A	Z	642
B	X	75345
B	Y	3452
B	Z	2457
C	X	624
C	Y	445
C	Z	5683

What I want to do is create a list which can produce the top category in each location. eg.

Location	Category
A	Y
B	X
C	Z

I have tried the following, but it always gives me the values for all locations A, B, and C combined:

=FirstSortedValue(Race,-Aggr(Count(DISTINCT SubID),Race))

Is there a way to do this?

Thanks all in advance!

LDK

Re: Find largest dimension within a dimension

tresB — Fri, 28 Feb 2014 09:19:40 GMT

Include the location dimension in Aggr and try like:

=FirstSortedValue(Race,-Aggr(Count(DISTINCT SubID),Location, Race))

Re: Re: Find largest dimension within a dimension

MK_QSL — Fri, 28 Feb 2014 09:37:35 GMT

See Attached...

Re: Find largest dimension within a dimension

er_mohit — Fri, 28 Feb 2014 09:44:50 GMT

Try this

FirstSortedValue(Category,-Aggr([Number of people],Locations,Category))

Re: Find largest dimension within a dimension

kumarnatarajan — Fri, 28 Feb 2014 09:45:54 GMT

Hi,

Try below script

Ex:

Aggr(NODISTINCT FirstSortedValue(Category, -[Number of people]),Locations)

Re: Find largest dimension within a dimension

Mon, 03 Mar 2014 01:04:42 GMT

Hi tresesco,

Thank you so much this helps!

LDK