# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

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

Tags (1)
1 Solution

Accepted Solutions
Valued Contributor III

## Re: How to balance amounts based on countries (netting solutions)

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:

"From Country",

"To Country",

Amount,

"From Country"&"To Country" AS Key

Resident DATA;

Drop Table DATA;

NettingPlus:

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

4 Replies
Highlighted
New Contributor III

## Re: How to balance amounts based on countries (netting solutions)

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

Valued Contributor III

## Re: How to balance amounts based on countries (netting solutions)

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:

"From Country",

"To Country",

Amount,

"From Country"&"To Country" AS Key

Resident DATA;

Drop Table DATA;

NettingPlus:

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

Valued Contributor

## Re: How to balance amounts based on countries (netting solutions)

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)

New Contributor

## Re: How to balance amounts based on countries (netting solutions)

Thank you, this is what we prefer!