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

[petter]

How did you create your table? In the load script or as a straight table?

[Anonymous]

Hi peter,

I have loaded the script to get the those table fields....

[petter]

You can do a resident load like this:

FINALTABLE:

LOAD

     If( Match( [Rollover Destination] ,'UNKNOWN' ,'COMPETITION - IBG' ) , 'Others' , [Rollover Destination] ) AS [Rollover Destination],

     [RTA $]

RESIDENT

    [SOURCETABLE];

You will have three rows in the FINALTABLE with 'Others' but they will sum up to one row in a Straight Table or a Pivot Table later in your UI.

If you want to sum the three rows in the load script you could do this:

FINALTABLE:

LOAD

     [Rollover Destination],

     Sum( [RTA $]

GROUP BY

    [Rollover Destination]

;

LOAD

     If( Match( [Rollover Destination] ,'UNKNOWN' ,'COMPETITION - IBG' ) , 'Others' , [Rollover Destination] ) AS [Rollover Destination],

     [RTA $]

RESIDENT

    [SOURCETABLE];

[avkeep01]

Hi Surya,

in the chart make the first expression as follows:

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

you can make the + 15 variable btw.

then make the seconde expression SUM([RTA $]).

on the presentation tab in the chart you can hide the first expression.

see my attachment for the example. I didn't hide the second expression yet.

[woshua5550]

if you prefer to deal with it at frontend , you can try expression like this

IF([Rollover Destination] = 'Others',Sum(TOTAL {<[Rollover Destination] = {'UNKNOWN','COMPETITION - IBG','Others'}>}[RTA $]), Sum({<[Rollover Destination] =- {'UNKNOWN','COMPETITION - IBG'}>}[RTA $]))

PFA

[Anonymous]

Hi Petter,

I am not looking to create 'Others' category in table.In the UI, the below option which is available in the Table.

There i wanted to add 'UNKNOWN' ,'COMPETITION - IBG' to Other defult category.please suggest?

[Anonymous]

Hi Dave,

The above expression removes the value associated to this UNKNOWN','COMPETITION - IBG categories .

But i want those values to be added to the Others category.

[Anonymous]

Hi Van

Can you please explain more....

the above expression its not working out

[woshua5550]

have you checked attachment ? it looks work as your wish