New table with design value

[#1]

Hi All !

I have the following table:

RouteString	TerminalName	RouteLineNumber	OperationName	=sum(Volume)
A-B-C-D	A	1	LOAD	200
A-B-C-D	B	2	UNLOAD	100
A-B-C-D	B	2	LOAD	300
A-B-C-D	C	3	UNLOAD	100
A-B-C-D	C	3	LOAD	150
A-B-C-D	D	4	UNLOAD	450

I need to create a new table with the calculation of Total Volume = Volume(where RouteLineNumber -1) - Volume(unload) + Volume(load)

The final result for example above should be:

RouteString	TerminalName	RouteLineNumber	Total Volume
A-B-C-D	A	1	200
A-B-C-D	B	2	400
A-B-C-D	C	3	450
A-B-C-D	D	4	0

Does anybody have some ideas how to manage the calculus?

Thanks a lot in advance for help!

[swuehl · Accepted solution]

Try a chart with your three dimensions  (RouteString, TerminalName, RouteLineNumber) and as expression:

=Rangesum(    

     Above(TOTAL Sum({<OperationName = {'LOAD'}>} Volume),0,rowno(TOTAL)),

-   Above(TOTAL Sum({<OperationName = {'UNLOAD'}>} Volume),0,rowno(TOTAL)),

)

[#3]

Thanks a lot!