How to use “last good” cell value to fill successive Cell’s missing values ?

Wed, 15 Sep 2010 15:35:48 GMT

Dear all, I need to use the latest good Column value to fill the subsequence Column if their value is missing.

For instance, for the following Data table, the Cells( L2,C3), ( L2,C4), ( L2,C5) should assume (display) the value of the Cell (L2,C2) which contains the latest "good Value" of C2L2

L	C1	C2	C3	C4	C5	C6
L1	C1L1	C2L1	C3L1	C4L1	C5L1	C6L1
L2	C1L2	C2L2				C6L2
L3	C1L3	C2L3	C3L3	C4L3	C5L3	C6L3
L4	C1L4	C2L4	C3L4	C4L4	C5L4	C6L4
L5	C1L5			C4L5	C5L5	C6L5
L6	C1L6	C2L6	C3L6	C4L6	C5L6	C6L6
L7	C1L7	C2L7	C3L7	C4L7	C5L7	C6L7
L8	C1L8	C2L8	C3L8	C4L8	C5L8	C6L8
L9	C1L9	C2L9	C3L9	C4L9	C5L9	C6L9
L10	C1L10					C6L10
L11	C1L11	C2L11	C3L11	C4L11	C5L11	C6L11

Due to the nature of data (Balance Financial Report) the information is displayed in a Pivot, of Balance Accounts (horizontally - 1^st Dimension) over Months (Vertically- 2^nd Dimension ) .

A simple solution would be to use the function Column() as in the expression:

If(IsNull(Data), Column(ColumNo()-1), Data)

but in a Pivot it doesn't work due to dimensionality used.

I've attached an example illustrating the problem.

Any suggestions?

Thanks in advance,

pmb

How to use “last good” cell value to fill successive Cell’s missing values ?

johnw — Wed, 15 Sep 2010 20:20:15 GMT

if(len(Data),Data,before(column(1)))

How to use “last good” cell value to fill successive Cell’s missing values ?

Thu, 16 Sep 2010 08:34:39 GMT

Great!

Thank you John & best regards,

Paulo

topic How to use “last good” cell value to fill successive Cell’s missing values ? in QlikView

How to use “last good” cell value to fill successive Cell’s missing values ?

How to use “last good” cell value to fill successive Cell’s missing values ?

How to use “last good” cell value to fill successive Cell’s missing values ?