Sum Field based on other two fields aggregated

[FelipeG]

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 🙂

[Kushal_Chawda · Accepted solution]

@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))

[FelipeG]

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...

[Kushal_Chawda]

@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