topic Re: Help in Rank function in QlikView

Help in Rank function

thomas_skariah — Thu, 20 Mar 2014 03:58:31 GMT

Hi All,

I have a pivot table using rank function to display top 15 brands country wise.

Here the problem I'm facing is some brand is not present in some country lik eg :-Aus doent have brand C which comes under rank 10 (See rank 10 not present in excel file)

I have to display 15 brands for each country, but because of some brands not present say Aus it showing only 13 instead of 15.

Can anyone suggest on this.

Attached is the sample excel file.

Formula for Rank:-=aggr(rank(sum([SALES])),BRAND)

Formula for Brand=if(aggr(rank(sum([SALES])),BRAND)<=15,BRAND)

Regards,

Tom

Re: Help in Rank function

Thu, 20 Mar 2014 04:02:48 GMT

Add Country to your aggregation. That should be fine.

Re: Re: Help in Rank function

thomas_skariah — Thu, 20 Mar 2014 07:13:44 GMT

Hi ,

Is there any way that if any of the countries have have brand less than 15, then also it should show 15 rows.With the rest of the row keep as blank?

Regards,

Tom

Re: Help in Rank function

MK_QSL — Thu, 20 Mar 2014 08:18:06 GMT

Your sample file is wrong.

how come AUS, Brand A is coming twice?

Re: Help in Rank function

thomas_skariah — Thu, 20 Mar 2014 08:29:22 GMT

Hi Manish,

I have mentioned only the initials .So its two different brand with same initials.Sorry for confusion.

Regards,

Tom

Re: Help in Rank function

Thu, 20 Mar 2014 08:32:43 GMT

You will have to address this at the script to insert a dummy record with a 0 value

Sent from my iPhone