
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sum Field based on other two fields aggregated
Hello Qlik community !!
I have a table consisting of 4 Fields: Date, Buyer, Seller, TotalAmount.
My data consists of transactions between the buyers and sellers which can be interchangeable, in other words, entity X can appear both as seller and buyer. What i want to do is rank the buyers and sellers as a single entity "Company" based on the Sum of the total amount, whether they acted as buyer or seller.
For example supposing I have this values in my Table:
Date | Buyer | Seller | Amount |
12/05/2020 | Apple | Amazon | 2000 |
13/05/2019 | Amazon | Apple | 2500 |
01/01/2020 | Apple | Microsoft | 1000 |
I want a table-chart that displays:
Rank | Company | Total Amount Transactions |
1 | Apple | 5500 |
2 | Amazon | 4500 |
3 | Microsoft | 1000 |
Will really appreciate it if someone can help me 🙂
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@FelipeG may be you can create a model like below with creating Flag for Buyer and seller in case you want to look at the individually.
LOAD
"Date",
Buyer as Company,
Amount,
'Buyer' as Type
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);
Concatenate
LOAD
"Date",
Seller as Company,
Amount,
'Seller' as Type
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);
Now you can create chart with Dimension company below measures
1) Sum(Amount)
2) rank(Sum(Amount))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@FelipeG may be you can create a model like below with creating Flag for Buyer and seller in case you want to look at the individually.
LOAD
"Date",
Buyer as Company,
Amount,
'Buyer' as Type
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);
Concatenate
LOAD
"Date",
Seller as Company,
Amount,
'Seller' as Type
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);
Now you can create chart with Dimension company below measures
1) Sum(Amount)
2) rank(Sum(Amount))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the answer, this works fine. Is there a way I dont have to replicate my data? Right now I dont have big volumes of data but in the near future i might...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@FelipeG Yes there is always a second way but that won't be flexible based on your requirements otherwise I would have suggested it at first place
