Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Netting dataset.PNG

Tags (1)
1 Solution

Accepted Solutions
Highlighted
mark6505
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:

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

    

4 Replies
onur0707
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

Highlighted
mark6505
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:

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

pschwegler003
New Contributor

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

Thank you, this is what we prefer!

Community Browser