Replacing 0 with last value in column per row

[#1]

Hi all,

I have this quite specific following problem and hope to find some help here:

What I have:

	Jan	Feb	Mar	Apr	Mai	Jun
Outlet 1	10	0	15	0	0	0
Outlet 2	0	15	0	0	10	0
Outlet 3	0	0	15	0	5	0
average	3,33	5	10	0	5	0

I like to replace 0 with the number from the column before (if that is also 0, than again the one before) per Outlet and getting the correct total average in the end.

What I want:

	Jan	Feb	Mar	Apr	Mai	Jun
Outlet 1	10	10	15	15	15	15
Outlet 2	0	15	15	15	10	10
Outlet 3	0	0	15	15	5	5
average	3,33	8,33	15	15	10	10

My goal is to show a chart with all months I have so far and with the correct total averange numbers.

Really thanks a lot for any help in advance!

[#2]

Try:

If(value=0, above(

[#3]

Try:

If(fieldname=0, above(fieldname), newfieldname)

[sunny_talwar]

What is the expression that you are using?

[#5]

If(fieldname=0, peek(fieldname), fieldname)

[#6]

Hi thanks, I tried this but it would just give me the value above once.

e.g. Outlet 1 in the example above would be like this:

from:

	Jan	Feb	Mar	Apr	Mai	Jun
Outlet 1	10	0	15	0	0	0

to:

	Jan	Feb	Mar	Apr	Mai	Jun
Outlet 1	10	10	15	15	0	0

instead of:

	Jan	Feb	Mar	Apr	Mai	Jun
Outlet 1	10	10	15	15	15	15

Also when I try to calculate the average QV wont identify one single Outlet have a 0 value in one month and just see the sum.

[#7]

Hi thanks, I tried this but it would just give me the value above once.

e.g. Outlet 1 in the example above would be like this:

from:

	Jan	Feb	Mar	Apr	Mai	Jun
Outlet 1	10	0	15	0	0	0

to:

	Jan	Feb	Mar	Apr	Mai	Jun
Outlet 1	10	10	15	15	0	0

instead of:

	Jan	Feb	Mar	Apr	Mai	Jun
Outlet 1	10	10	15	15	15	15

Also when I try to calculate the average QV wont identify one single Outlet have a 0 value in one month and just see the sum. In that case the Average is wrong since QV didnt count all Outlets.

[#8]

In the end I want a graph to show all months I have so far and with the correct total averange numbers:

sum(Products) / count( Outlet )  with month as my dimension.

if I dont have a number of one month per Outlet I like to take the number of the last month with a number.