In/out-flow in table

[HillVi]

Hi all!

I am trying to create a table where the user will be able to see how money flows in and out of different locations.

In the source table I have where the money flows from and to as well as the amount  like this:

From	To	Amount
A	B	100
A	C	200
B	D	50
C	-	-
D	C	100

However I would like to be able to calculate the inflow aswell like this:

From	To	Amount out	Amount In
A	B	100	0
A	C	200	0
B	D	50	100
C		-	300
D	C	100	50

Any tips on how to achieve this?

Thanks!!

[rubenmarin · Accepted solution]

Hi, this can be easy if preloaded in script:

data:
LOAD * INLINE [
    From, To, Amount
    A, B, 100
    A, C, 200
    B, D, 50
    C, -, -
    D, C, 100
];

Left Join  (data)
LOAD To as From,
	Sum(Amount) as [Flow In]
Resident data group by To;

On design would be a lot harder, note that set analysis is applied before the table is calculated, so it won't work row by row.

[HillVi]

Thank you and sorry for the late reply!

Preloading in script would be a great solution.

However, in this particular case the outflow is a calculation using a variable. Thus I would like to be able to do something like sum({<From={To}>}Amount), ie fetching the outflow from the row where From is equal to To in the current row. 

I am realizing this might however not be possible..

[rubenmarin]

Hi, I think that at some point you'll need help of the data model (script) to help the calculations of the table.