topic Complex filtering of data sets in QlikView

Complex filtering of data sets

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

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

Re: Complex filtering of data sets

vishsaggi — Thu, 03 May 2018 19:01:36 GMT

Just use this in your straight table like

Dim: Neighborhood

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

Re: Complex filtering of data sets

MarcoWedel — Thu, 03 May 2018 19:54:23 GMT

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

Re: Complex filtering of data sets

vishsaggi — Thu, 03 May 2018 20:13:56 GMT

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

Add neighborhood as your field

Then go to expression tab

Re: Complex filtering of data sets

vishsaggi — Thu, 03 May 2018 20:21:42 GMT

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

Re: Complex filtering of data sets

terrusie — Thu, 03 May 2018 21:03:50 GMT

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.

Re: Complex filtering of data sets

terrusie — Thu, 03 May 2018 21:05:58 GMT

Yes,

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