Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
Specialist

## sum of the tables

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

category, amount

AVG share, 100
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!

Labels (3)

• ### Script

1 Solution

Accepted Solutions
Specialist III

Hi @alexpanjhc , please try this

Data:
Category, Amount
AVG share, 100
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:
'Net Increase' as Dim_Line,
sum(Amount) as Amount_Line
Resident Data
Where
Category <> 'AVG share';

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

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

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

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

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

drop table Aux;

'Total' as Dim_Line,
sum(Amount_Line) as Amount_Line
Resident New_Table
Where

output :

QFabian
2 Replies
Specialist III

Hi @alexpanjhc , please try this

Data:
Category, Amount
AVG share, 100
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:
'Net Increase' as Dim_Line,
sum(Amount) as Amount_Line
Resident Data
Where
Category <> 'AVG share';

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

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

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

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

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

drop table Aux;

'Total' as Dim_Line,
sum(Amount_Line) as Amount_Line
Resident New_Table
Where

output :

QFabian
Champion III

Two Options to achieve this

1)Use valuelist()

Measure =

and so on ....

Option 2

2)  Pick() with dummy dimension

Create a dummy dimension table

DummyDim:

Autogenerate  5;

Chart Dimension

Measure

=Pick(Dim

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

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

,  and so on )

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Tags
Community Browser