topic Re: Top N Product Sales by Country and Month in QlikView

Top N Product Sales by Country and Month

rileymd88 — Wed, 31 Aug 2016 08:23:56 GMT

Hi All,

I am trying to look at my sales data overall across different countries, but restricted to the top 50 selling products per country and month. I am able to get this view in a straight table, however only when I bring in all dimensions (Country, Month and Product ID). Is there anyway to get this same number in a straight table but only using the dimension Country?

Here is the formula I am using at the moment:

=If(Rank(Aggr(Sum(Sales), Country, [Month], [Product ID]), 1) <=50, Sum(Sales))

Thanks,

Re: Top N Product Sales by Country and Month

effinty2112 — Wed, 31 Aug 2016 09:43:47 GMT

Hi Riley,

You can hide dimension columns you don't want to see.

Go to the Presentation tab and select Hide Column for the dimensions you want to be invisible.

cheers

Andrew

Re: Top N Product Sales by Country and Month

sunny_talwar — Wed, 31 Aug 2016 09:54:03 GMT

How about this:

Sum(Aggr(If(Rank(Sum(Sales), 1) <= 50, Sum(Sales)), Country, Month, [Product ID]))

Re: Top N Product Sales by Country and Month

rileymd88 — Wed, 31 Aug 2016 10:00:01 GMT

Hi Andrew,

If I do this then I the country will repeat itself for every unique Country, Month and Product ID combination.

Re: Top N Product Sales by Country and Month

effinty2112 — Wed, 31 Aug 2016 10:08:17 GMT

Hi Riley,

Yeah ... I didn't think it through .

cheers

Andrew