Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MK_QSL
MVP
MVP

TOP 5 in PIVOT TABLE

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 CodeSales OperatorMonthTotalMayAprilAugustJulyJuneSeptember
Branch 01pshan 105,70615,13915,87627,37618,80117,56710,948
Branch 01keitr 72,90212,9199,57714,46010,90812,48112,556
Branch 01tferg 64,92014,59613,6806,73916,98511,4791,442
Branch 01fiqba 57,9378,5056,77110,87810,7808,71412,289
Branch 01antoc 42,6018,3586,0457,2339,7116,0825,172
12 Replies
mjm
Employee
Employee

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.

MK_QSL
MVP
MVP
Author

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...

sadickbasha
Partner - Contributor III
Partner - Contributor III

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

israrkhan
Specialist II
Specialist II

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

Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
MK_QSL
MVP
MVP
Author

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

israrkhan
Specialist II
Specialist II

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

2013-11-05_164502.png

Anonymous
Not applicable

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

MK_QSL
MVP
MVP
Author

$(=GetCurrentField(Selection))

Use Above instead of your Dimensions below...

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