Above row value with dimension

[#1]

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

[PrashantSangle]

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,

[#3]

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 ... 

[petter]

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.

[pathiqvd]

Hi,

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

Regards,

[#6]

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

[#7]

by doing small change it worked fine.

Thanks....