topic Re: Formula with dependence from previous value in QlikView

Formula with dependence from previous value

Tue, 17 May 2016 13:15:32 GMT

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!

Re: Formula with dependence from previous value

Anonymous — Tue, 17 May 2016 15:41:39 GMT

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

Re: Formula with dependence from previous value

settu_periasamy — Tue, 17 May 2016 16:04:15 GMT

Have you tried expression with column labels.. like

Sum (UNLOAD) / Above ([Current Volume])

Re: Formula with dependence from previous value

Wed, 18 May 2016 07:24:05 GMT

The field [Current Volume] not found

Re: Formula with dependence from previous value

Wed, 18 May 2016 07:28:20 GMT

Above here doesn't work, it's "-" for all rows

Re: Formula with dependence from previous value

settu_periasamy — Wed, 18 May 2016 09:49:18 GMT

Hi,

Check the Attachment..

Re: Formula with dependence from previous value

Wed, 18 May 2016 11:59:27 GMT

Thank you ever so much!