Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: **Register**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- sum of the tables

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

alexpanjhc

Specialist

2022-03-31
04:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

sum of the tables

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!

290 Views

1 Solution

Accepted Solutions

QFabian

Specialist III

2022-03-31
06:30 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 :

QFabian

260 Views

2 Replies

QFabian

Specialist III

2022-03-31
06:30 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 :

QFabian

261 Views

vinieme12

Champion III

2022-03-31
09:36 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 )

Vineeth Pujari

If a post helps to resolve your issue, please accept it as a Solution.

If a post helps to resolve your issue, please accept it as a Solution.