Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have the following table:
RouteString | TerminalName | RouteLineNumber | LOAD | UNLOAD |
A-B-C-D-E-F | A | 1 | 100 | 0 |
A-B-C-D-E-F | B | 2 | 0 | 20 |
A-B-C-D-E-F | C | 3 | 10 | 50 |
A-B-C-D-E-F | D | 4 | 5 | 15 |
A-B-C-D-E-F | E | 5 | 20 | 45 |
A-B-C-D-E-F | F | 6 | 0 | 5 |
where I added the column (Current Volume) with formula:
=Rangesum(Above(TOTAL Sum({< OperationName = {'LOAD'}>} Volume),0,max(RouteLineNumber)),) - Rangesum(Above(TOTAL Sum({<OperationName = {'UNLOAD'}>} Volume),0,max(RouteLineNumber)))
It works correctly and I have:
RouteString | TerminalName | RouteLineNumber | LOAD | UNLOAD | Current Volume |
A-B-C-D-E-F | A | 1 | 100 | 0 | 100 |
A-B-C-D-E-F | B | 2 | 0 | 20 | 80 |
A-B-C-D-E-F | C | 3 | 10 | 50 | 40 |
A-B-C-D-E-F | D | 4 | 5 | 15 | 30 |
A-B-C-D-E-F | E | 5 | 20 | 45 | 5 |
A-B-C-D-E-F | F | 6 | 0 | 5 | 0 |
Is it possible to add into the table one more column with values = UNLOAD / Current Volume with RouteLineNumber-1 ?
I.e. I want to receive the column with name "Share":
RouteString | TerminalName | RouteLineNumber | LOAD | UNLOAD | Current Volume | = | Share | |
A-B-C-D-E-F | A | 1 | 100 | 0 | 100 | 0 | 0 | |
A-B-C-D-E-F | B | 2 | 0 | 20 | 80 | 20/100 | 0,2 | |
A-B-C-D-E-F | C | 3 | 10 | 50 | 40 | 50/80 | 0,625 | |
A-B-C-D-E-F | D | 4 | 5 | 15 | 30 | 15/40 | 0,375 | |
A-B-C-D-E-F | E | 5 | 20 | 45 | 5 | 45/30 | 1,5 | |
A-B-C-D-E-F | F | 6 | 0 | 5 | 0 | 5/5 | 1 |
Thanks in advance for any ideas!
Hi
try this ..
TEMP1:
LOAD RouteString,
TerminalName,
RouteLineNumber,
LOAD,
UNLOAD,
[Current Volume],
UNLOAD/Previous([Current Volume]) AS SHARE
FROM
[https://community.qlik.com/thread/217211]
(html, codepage is 1252, embedded labels, table is @3);
Have you tried expression with column labels.. like
Sum (UNLOAD) / Above ([Current Volume])
The field [Current Volume] not found
Above here doesn't work, it's "-" for all rows
Hi,
Check the Attachment..
Thank you ever so much!