topic Re: Replacing 0 with last value in column per row in QlikView

Replacing 0 with last value in column per row

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

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

What I have:

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

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!

Re: Replacing 0 with last value in column per row

Wed, 01 Jun 2016 16:44:34 GMT

Try:

If(value=0, above(

Re: Replacing 0 with last value in column per row

Wed, 01 Jun 2016 16:46:24 GMT

Try:

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

Re: Replacing 0 with last value in column per row

sunny_talwar — Wed, 01 Jun 2016 17:04:32 GMT

What is the expression that you are using?

Re: Replacing 0 with last value in column per row

Wed, 01 Jun 2016 17:12:28 GMT

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

Re: Replacing 0 with last value in column per row

Thu, 02 Jun 2016 07:56:27 GMT

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.

Re: Replacing 0 with last value in column per row

Thu, 02 Jun 2016 07:59:49 GMT

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.

Re: Replacing 0 with last value in column per row

Thu, 02 Jun 2016 08:09:06 GMT

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.