Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)),
)
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)),
)
Thanks a lot!