Dimension - all Postal Codes of Top 3 Cities

[richters]

Hi all,

I want to create a dimension with all postal codes of the top 3 cities regarding sales.

I ended up creating a dimension with the top 3 cities:

=aggr(if(rank(sum({<$(vYTD)>}sales)) <=3, city),city)

But how can I now get all of the related postal codes of these 3 cities?

Thanks for any help!

[sunny_talwar]

Have you tried this

=Aggr(If(Rank(Sum(TOTAL <city> {<$(vYTD)>} sales)) <= 3, PostalCode), city, PostalCode)

[richters]

Aparently, that's what I thought would be right. But aparenty, Qlik is giving me much more (I think nearly all) postal codes back...

[sunny_talwar]

Would you be able to share a sample to show the issue using the above expression?

[richters]

Here you are, it is simplified on the problem.

[sunny_talwar · Accepted solution]

Try this

=If(Aggr(NODISTINCT Rank(Sum(Sales)), City) <= 3, PostalCode)

[richters]

Wow, thanks. Exactly!