Table customization

[Anonymous]

Good Morning,

I have created table in which My table shows the Top 15 client trends and remaining as Others.I just wanted to move the Unknown & Competion IBG from the Top 15 and add to the Others irrespective of the RTA$(shown below).Please suggest?

Rollover Destination	RTA $
FI	$176,368,433
CH	$49,327,118
VAN	$44,383,002
MOR	$30,092,771
UNKNOWN	$22,230,271
BAN	$21,506,932
AME	$21,052,990
EDW	$17,325,477
MERR	$16,351,848
AMER	$14,120,564
WELLS	$12,825,707
COMPETITION - IBG	$12,237,331
LINS	$8,709,050
UBS	$6,851,787
Ohters	$71,750,223

[Anonymous]

Hi Dav,

can u share the attachment again? becoz i am not see any attached file

[woshua5550]

that probably because you were reading message in your inbox rather than in our discussion page , try open this

[avkeep01]

Hi Surya,

I made a straight tabel. I set [Rollover Destination] as dimension. The first expression is a rank over the [RTA $].

The Expression is build of the following components:

1. RANK(SUM([RTA $]),4)) -> the ,4 makes the rank following up on the previous row in case the values are the same. I.e. if the first value equals the second then with ,4 you set them as 1 and 2, otherwise you would have 1 for both of the values.

2. Then I made an if statement where I define the [Rollover Destinations] that needs to be excluded:

IF(MATCH([Rollover Destination],'COMPETITION - IBG','UNKNOWN'), <true>, <false>

When the [Rollover Destionation] = competition ibg or unknown then I add +15 the the rank of the [Rollover destiniation]. I choose + 15 because then they will drop of the first 15 destinations you want to see. You can set the number higher if you want.

3. Next step is aggregating the data per [Rollover Destination] and I need to have a number per destionation. so I used SUM(AGGR(<expression>,[Rollover Destination])). I also could have used MAX or MIN or Only. I just needed a function generating 1 number per destinations.

The [Dimension Limits] looks at the first expression. So setting the first Dimension limit showing only the first 15 values makes competition ibg and unknown appearing under "others".

Then you don't want to see the rank expression, so you hide the column at the presentation tab.

Can you tell me what isn't working out in your application?

[avkeep01]

I made the example in Qlikview. I think you were working with Sense. I'll need to adjust the solution for Sense.

[avkeep01]

Hi Surya,

My expression works when you select "Bottom" instead of "Top". But you can't hide columns in Sense

[Anonymous]

Hi Dav

Your solution is removing the RTA$ value from the entire table output....After using your logic i am finding those difference in the data and that is equal to the Others category

Rollover Destination	RTA $	Rollover Destination	RTA $
FI	$176,368,433	FI	$176,368,433
CH	$49,327,118	CH	$49,327,118
VAN	$44,383,002	VAN	$44,383,002
MOR	$30,092,771	MOR	$30,092,771
UNKNOWN	$22,230,271	BAN	$21,506,932
BAN	$21,506,932	AME	$21,052,990
AME	$21,052,990	EDW	$17,325,477
EDW	$17,325,477	MERR	$16,351,848
MERR	$16,351,848	AMER	$14,120,564
AMER	$14,120,564	WELLS	$12,825,707
WELLS	$12,825,707	LINS	$8,709,050
COMPETITION - IBG	$12,237,331	UBS	$6,851,787
LINS	$8,709,050	E TRA	$6,784,443
UBS	$6,851,787	NEW	$5,281,292
Ohters	$71,750,223	Others	$59,618,668
	$525,133,505		$490,600,082

[Anonymous]

Hi Van Your expression is working but its giving the Rank...I am using this expression in chart...I have a data table in which i have to populate those data...So how can i get my RTA$ value instead of Rank... Can u plz share the expression directly instead of sharing the work book... thanks for explaining in detail

[avkeep01]

Hi Surya,

Expression used:

MIN(AGGR(IF(MATCH([Rollover Destination],'COMPETITION - IBG','UNKNOWN'), RANK(SUM([RTA $]),4)+15,RANK(SUM([RTA $]),4)),[Rollover Destination]))

You will need to have the rank column. Can't find any other way for that. I made a second expression:

Sum([RTA $])

Together they will form this chart:

[Anonymous]

My requirement is simple.... I just wanted to view top 15 contributors apart from COMPETITION - IBG & UNKNOWN.Those contributions has to be directly added to the 'Others' in default

[avkeep01]

You could try this script in the script editor:

SourceTable:

LOAD * INLINE [

Rollover Destination, RTA $

FI, 176368433

CH, 49327118

VAN, 44383002

MOR, 30092771

UNKNOWN, 22230271

BAN, 21506932

AME, 21052990

EDW, 17325477

MERR, 16351848

AMER, 14120564

WELLS, 12825707

COMPETITION - IBG, 12237331

LINS, 8709050

UBS, 6851787

];

// Create rank based on RTA $, but if [Rollover Destination] equals COMPETITION - IBG or UNKNOWN then raise the rank.

temp_Table:

LOAD

[Rollover Destination],

IF(MATCH([Rollover Destination],'COMPETITION - IBG','UNKNOWN'),ROWNO()+15,ROWNO()) AS Rank,

[RTA $]

RESIDENT SourceTable

ORDER BY [RTA $] DESC;

DROP TABLE SourceTable;

// Sort by rank, name everything with rank higher then 15 others (just like in the front end) and group by the first 15 [Rollover Destinations], the rest will be shown as others.

DataTable:

NOCONCATENATE LOAD

IF(Rank >15,'Others',[Rollover Destination]) AS [Rollover Destination],

SUM([RTA $]) AS [RTA $]

RESIDENT temp_Table

GROUP BY IF(Rank >15,'Others',[Rollover Destination])

ORDER BY Rank;

DROP TABLE temp_Table;