sum of the tables

[alexpanjhc]

hi I have a calculated table and i am making it easier as following

LOAD * Inline [
category, amount

AVG share, 100
Administration, -43286
Legal and professional , -186008
Audit , 9165
Organisational , -31440
Depositary fees, -9542
Directors' fees and expenses, -18786
Custody fees ,-13048
Income from swaps, 2566591
Management fees, -354150
Net foreign exchange loss, -88970
A, 5555292
B, 0
];

 the new table needs to show

the sum all of the categories except avg share  as "Net Increase"

  sum of A+B+Income from swaps  as  "adjustment"

Avg Share as line 3

and line1/line3 as line4

total line shows adjustment +Net increase.

Is it possible?

thanks!

[QFabian · Accepted solution]

Hi @alexpanjhc , please try this

Data:
LOAD * Inline [
Category, Amount
AVG share, 100
Administration, -43286
Legal and professional , -186008
Audit , 9165
Organisational , -31440
Depositary fees, -9542
Directors' fees and expenses, -18786
Custody fees ,-13048
Income from swaps, 2566591
Management fees, -354150
Net foreign exchange loss, -88970
A, 5555292
B, 0
];

New_Table:
Load
'Net Increase' as Dim_Line,
sum(Amount) as Amount_Line
Resident Data
Where
Category <> 'AVG share';

Load
'Adjustment' as Dim_Line,
sum(Amount) as Amount_Line
Resident Data
Where
match(Category, 'Income from swaps', 'A', 'B');

Load
'AVG share' as Dim_Line,
sum(Amount) as Amount_Line
Resident Data
Where
Category = 'AVG share';

Aux:
Load
sum(Amount_Line) as Amount_Line1
Resident New_Table
Where
Dim_Line = 'Net Increase';

Left Join
Load
sum(Amount_Line) as Amount_Line2
Resident New_Table
Where
Dim_Line = 'AVG share';

Load
'Line 4' as Dim_Line,
Amount_Line1 / Amount_Line2 as Amount_Line
Resident Aux;

drop table Aux;

Load
'Total' as Dim_Line,
sum(Amount_Line) as Amount_Line
Resident New_Table
Where
match(Dim_Line, 'Adjustment', 'Net Increase');

output :

[vinieme12]

Two Options to achieve this

1)Use valuelist()

Dimension = Valuelist('NetIncrease','Adjustment','AvgShare',Total')

Measure =

if(Valuelist('NetIncrease','Adjustment','AvgShare',Total') = 'NetIncrease' , sum({<category-={'AvgShare'}>}amount)

,if(Valuelist('NetIncrease','Adjustment','AvgShare',Total') = 'Adjustment' , sum({<category={'A','B','Income from swaps'}>}amount)

,if(Valuelist('NetIncrease','Adjustment','AvgShare',Total') = 'AvgShare' , sum({<category={'AvgShare'}>}amount)

and so on ....

Option 2

2)  Pick() with dummy dimension

Create a dummy dimension table

DummyDim:

load  iterno() as Dim

Autogenerate  5;

Chart Dimension

=Pick(Dim,'NetIncrease','Adjustment','AvgShare',Total') 

Measure

=Pick(Dim

,sum({<category-={'AvgShare'}>}amount)  // NetIncrease  in same order as per list above in Pick()

,sum({<category={'A','B','Income from swaps'}>}amount)  // adjustment

,sum({<category={'AvgShare'}>}amount)  // avg share

,  and so on )