Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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,
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 ...
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.
Hi,
Use "before" in place of "Above" function.
Regards,
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 |
by doing small change it worked fine.
Thanks....