Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
pschwegler003
Contributor II
Contributor II

How to balance amounts based on countries (netting solutions)

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.

Netting dataset.PNG

1 Solution

Accepted Solutions
Mark_Little
Luminary
Luminary

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

    

View solution in original post

4 Replies
L_Hop
Creator
Creator

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

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

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
Contributor II
Contributor II
Author

Thank you, this is what we prefer!