Complex filtering of data sets

[terrusie]

I'm sure there's an easy answer for this, but I'm having a hard time putting the right conditions together.

With the data table below, what I'm trying to do is make a list box that lists the people who have the most marbles in their neighborhood.

Neighborhood	Number of Marbles	Category 3
A	6	Cindy
A	1	John
A	3	Mary
B	10	David
B	11	Samantha
C	4	Jerry
C	15	Martha
C	7	Larry
C	9	Henry

In other words:

Neighborhood	Header 2
A	Cindy
B	Samantha
C	Martha

[vishsaggi · Accepted solution]

Just use this in your straight table like

Dim: Neighborhood

Expr: = FirstSortedValue([Category 3], -[Number of Marbles])

[MarcoWedel]

Hi,

one solution using a listbox could be:

If you want to select names instead of neighborhoods when clicking one value, you could instead use an expression like:

=Aggr(Only({$<[Category 3]={"=Index(Concat(total Aggr(FirstSortedValue([Category 3],-[Number of Marbles]),Neighborhood),'/'),[Category 3])"}>} [Category 3]),[Category 3])

hope this helps

regards

Marco

[vishsaggi]

Referring to Marco's expr in Listbox you can also try like below

Add neighborhood as your field

Then go to expression tab

[vishsaggi]

Oops Ignore this. This does not work when you select in the Listbox. Follow Marco's expression.

[terrusie]

Marco,

While Vishwarath's answer was sufficient, I'm curious as to the power that your solution adds. When/If I get around to testing it out, I'll post a response here.

[terrusie]

Yes,

Selecting in the listbox only isolates the Neighborhood.  It does not isolate the scope of the most marbles.