topic Re: Dimension - all Postal Codes of Top 3 Cities in QlikView

Dimension - all Postal Codes of Top 3 Cities

richters — Mon, 16 Jul 2018 11:50:18 GMT

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 — Mon, 16 Jul 2018 11:54:29 GMT

Have you tried this

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

richters — Mon, 16 Jul 2018 12:10:18 GMT

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 — Mon, 16 Jul 2018 12:13:19 GMT

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

richters — Mon, 16 Jul 2018 12:34:41 GMT

Here you are, it is simplified on the problem.

sunny_talwar — Mon, 16 Jul 2018 12:41:06 GMT

Try this

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

richters — Tue, 17 Jul 2018 05:04:03 GMT

Wow, thanks. Exactly!