How to balance amounts based on countries (netting solutions)

[pschwegler003]

Hi Community ,

based on the dataset below I am trying to get an overview of the total transactions. First I would like to sum all transactions and second I want to make an overview where I can see the total transaction between the countries. I am having trouble with scripting this in Qlik Sense. Anyone with an idea? Thanks in advance.

[L_Hop]

Hi Pascal;

I assume you want pivot table;

first, add 'From Country'  & 'To Country' as a dimension to the table.

Second, add 'sum(Amount)' as a measure, you will able to see what you want.

regards;

O.Y

[Mark_Little · Accepted solution]

Hi Pascal,

Try Something like

DATA:

LOAD * INLINE [

    From Country, To Country, Amount

    A, B, 1000

    B, A, 200

    A, C, 100

    A, B, 500

    B, A, 100

];

NoConcatenate

NettingPlus:

Load

"From Country",

"To Country",

Amount,

"From Country"&"To Country" AS Key

Resident DATA;

Drop Table DATA;

NettingPlus:

Load

Amount *-1 AS OFFSet,

"To Country"&"From Country" AS Key

Resident NettingPlus;

Then Set Analysis is SUM(AMOUNT)

Then

IF(SUM(Amount )+SUM(OFFSet) > 0,

SUM(Amount )+SUM(OFFSet))

Mark

[zebhashmi]

Sum({<From Country = {'A'}, To Country = {'B'}>}Amount)

and

Sum({<From Country = {'A'}, To Country = {'B'}>}Amount)-Sum({<From Country = {'B'}, To Country = {'C'}>}Amount)

[pschwegler003]

Thank you, this is what we prefer!