topic Re: Above row value with dimension in QlikView

Above row value with dimension

Tue, 03 Jan 2017 06:12:34 GMT

Dear All,

am created the below pivot table with dimensions S.No., Particulars & Date and with 3 Expressions

Problem : if Rate / Unit is blank for particular day we need to take previous Rate / Unit

Note : i can't use set analysis because Rate / Unit Expression formula is very big so trying to use above() function or to read previous column (1) value, but not able to get the same

Tried Expressions :

=aggr(rangesum(above(column(1),1,2)),[S.No],Particulars,Period)

= rangesum(above(column(1),1,2))

	Period	01-12-2016			02-12-2016
S.No.	Particulars	Rate / Unit	CF	Rate / T	Rate / Unit	CF	Rate / T
1	abcd	1545.70	0.75	1159.61		0.98

Re: Above row value with dimension

PrashantSangle — Tue, 03 Jan 2017 06:36:39 GMT

Hi,

In script you can use peek()

for example:

Test:

LOAD ID,if(len(trim(Sales))=0,Peek(Sales),Sales) as Sales,UPPER(NAME) as NAME INLINE [

ID,Sales,NAME

1,100,PRASHANT

2,,mahesh

3,,c

4,400,d

];

Regards,

Re: Above row value with dimension

Tue, 03 Jan 2017 06:40:45 GMT

As i already explained in my previous post that Rate / Unit is an expression with big formula calculates run time based on user selection criteria so can't write in Script.

Thanks ...

Re: Above row value with dimension

petter — Tue, 03 Jan 2017 06:54:43 GMT

You will have to use Before() instead of Above() in a pivot table if you are referring to a column to the left.

So your expression should have something like this pattern:

If( IsNull( Rate ) , Before( Column(1) ) , <Complex-Rate-Unit-Calc> )

The second and third parameters for Before() aren't necessary because the second tells the offset which defaults to 1 and the third is the number of values (count) that you want in the result and that should be only 1 which is also default. The same goes for Above() which you used.

Re: Above row value with dimension

pathiqvd — Tue, 03 Jan 2017 07:28:19 GMT

Hi,

Use "before" in place of "Above" function.

Regards,

Re: Above row value with dimension

Tue, 03 Jan 2017 07:34:06 GMT

Hi,

Thanks for response, i tried with your formula

its working if i add a new expression field as testing as shown in below table but it is not working when am writing the same style in expression Rate / Unit

	Period	01-12-2016				02-12-2016
S.No.	Particulars	Rate / Unit	CF	Rate / T	Testing	Rate / Unit	CF	Rate / T	Testing
1	abcd	1545.70	0.75	1159.61			0.98		1545.70

Re: Above row value with dimension

Tue, 03 Jan 2017 08:05:57 GMT

by doing small change it worked fine.

Thanks....