topic Re: TOP 5 in PIVOT TABLE in QlikView

TOP 5 in PIVOT TABLE

MK_QSL — Tue, 05 Nov 2013 11:42:25 GMT

I have sales table for different branches…

What I need is a pivot table with Dimensions

Branch Code

Sales Operator

Month

Expression : SUM(Sales)

I need a pivot table showing top 5 sales operator of each branch and their monthly sales achievements.

If I select Branch 01... result would be something like below...

Branch Code	Sales Operator	Month	Total	May	April	August	July	June	September
Branch 01	pshan		105,706	15,139	15,876	27,376	18,801	17,567	10,948
Branch 01	keitr		72,902	12,919	9,577	14,460	10,908	12,481	12,556
Branch 01	tferg		64,920	14,596	13,680	6,739	16,985	11,479	1,442
Branch 01	fiqba		57,937	8,505	6,771	10,878	10,780	8,714	12,289
Branch 01	antoc		42,601	8,358	6,045	7,233	9,711	6,082	5,172

Re: TOP 5 in PIVOT TABLE

mjm — Tue, 05 Nov 2013 11:51:06 GMT

Hello,

looking at your file I think I have achieved what you need. This is with the following sort:

Branch Code (no sorting selected)

Sales Operator (expression - sum(Sales) Descending

Month (no sorting)

This sorts according to the top sales person.

In a pivot table itself, there is no option to reduce to just show top 5. However, you could consider another chart (bar chart) where you can restrict presentation to only show 5. (Enable x axis scrollbar)

I have attached my example. I hope this helps. Please mark as answered if this helps.

Re: TOP 5 in PIVOT TABLE

MK_QSL — Tue, 05 Nov 2013 12:02:47 GMT

Thanks for your reply but this will not fulfill my requirements.

There should be sum RANK, AGGR and IF combination which will do my help...

Re: TOP 5 in PIVOT TABLE

sadickbasha — Tue, 05 Nov 2013 12:08:21 GMT

Even though you can't display only top 5 sales as there is dimension limits in Pivot table.

Re: Re: TOP 5 in PIVOT TABLE

israrkhan — Tue, 05 Nov 2013 12:08:23 GMT

hope this expression will work ..

Add calculated dimension, instead of [Sales Operator]

=If(Aggr(Rank(Sum(Sales)), [Sales Operator])<=5, [Sales Operator], 'Others')

you will have 6 rows, 5 top and 1 for others.

If you dont want to show others in the pivot table, then use 'Null' like below

=If(Aggr(Rank(Sum(Sales)), [Sales Operator])<=5, [Sales Operator], 'Null')

and check suppress null value on presentation tab, you will see exactly 5 rows.

Hope it helps...

Khan

Re: TOP 5 in PIVOT TABLE

Gysbert_Wassenaar — Tue, 05 Nov 2013 12:27:47 GMT

See attached qvw.

Re: TOP 5 in PIVOT TABLE

MK_QSL — Tue, 05 Nov 2013 12:36:33 GMT

Hi Khan,

Thanks for your reply. I have done it my self by using the same by below expression... your expression is not working...

=IF(AGGR(RANK(SUM(Sales)),[Branch Code],[Sales Operator])<=5, [Sales Operator])

One more question.... how can I color the dimension cell ?

If May sale for particular Operator is higher in respective branch than previous month i.e. April... the background color should be Green or Red

Re: TOP 5 in PIVOT TABLE

israrkhan — Tue, 05 Nov 2013 12:46:01 GMT

Hi Manish,

Sure any expression will not work exactly, without seeing data model.

but meant to give an idea, and off course you have to make little changes in that, but that helped you..

for background color, put your condition here in background color, see the image

Re: TOP 5 in PIVOT TABLE

Anonymous — Tue, 28 Jan 2014 05:46:30 GMT

if i have a cyclic group in dimention then in that case how should i give calculated dimention?

Re: TOP 5 in PIVOT TABLE

MK_QSL — Tue, 28 Jan 2014 06:04:16 GMT

$(=GetCurrentField(Selection))

Use Above instead of your Dimensions below...

=IF(AGGR(RANK(SUM(Sales)),[Branch Code],[Sales Operator])<=5, [Sales Operator])

Re: TOP 5 in PIVOT TABLE

Anonymous — Tue, 28 Jan 2014 06:13:37 GMT

I have used below expression but it is not wrking

=if(AGGR(RANK(SUM(Sales,GetCurrentField('GroupName')<=5,GetCurrentField(GroupName'))

Re: TOP 5 in PIVOT TABLE

MK_QSL — Tue, 28 Jan 2014 06:33:33 GMT

Try this

if(Aggr(Rank(SUM(Sales)),$(=GetCurrentField(GroupName)))<=5, $(=GetCurrentField(GroupName))

Re: TOP 5 in PIVOT TABLE

vipin_mishra479 — Tue, 28 Jan 2014 06:50:28 GMT

Hi manish,

Use This qvw.