How to count items related to a count of market?

[davidl60]

Hello All,

I'm new on Qlikview, already found a lot of answers to my questions on this forum , but this time I don't .(maybe because I don't know how phrase my question - see the topic title ).

An example is better than thousands words.

I have a table of items, market (where the item is available).

items	Markets
item A	France
item A	Croatia
item A	Belgium
item B	England
item B	Uruguay
item C	Argentina
item C	Brazil
item D	Russia
item D	Sweden
item D	Switzerland
item E	Iceland
item F	Germany
item G	Spain

I know how to count the number of market per item.

items	count Markets
item A	3
item B	2
item C	2
item D	3
item E	1
item F	1
item G	1

but what I want as a result is a list box like the below, where I could select the 'count market' and display a table of items related to.

count Markets	count items
3	2
2	2
1	3

2 products are available in 3 markets, 2 in 2 markets and 3 in 1

If I select count Market = 1, I want the table

items
item E
item F
item G

I have no clue how to do this.

I searched on this foum but didn't find the right way to phrase this.

Thank you in advance for your help.

David

[CarmenReilly]

Hi David,

Do you want it to display the list of items related to in the same table or in a separate table?

-Carmen

[vishsaggi]

may be this?

Add first expr in calculated dimension and 2nd expression in expression tab.

Then add Table box object and add items as your field. Like below

Then click on your dimension and see.

[Anonymous]

Hello David,

PFA.

[davidl60]

Hello Vishwarath,

That's exactly what I needed. It works perfectly well.

I now need to understand Aggr - I knew this function was involved but I didn't know how

Thank you very much.

David

[davidl60]

Hi Carmen,

In a separate one.

Solution given by Viswarath is perfect for what I needed.

Thank you for having had a look to my question.

David

[vishsaggi]

Glad it helped. So Aggr() is more like a Group By function. So when you are trying to use any aggregate functions in Dimensions you need to use Aggr() function to support these aggregate functions. And your TOTAL qualifier respects the selections made in listbox.