How to Find last value in Table using Bottom function

[#1]

Hello,

I'm trying to find a way to return the last valid value in a Straight table Column  BCWP

in this example  its: 20,770 

Would like to find a clever way to get the value without having to hard code the 6 since its located at this row from bottom.

Bottom(Sum( EV_TBC * %Complete),6 ,0)   I want to use this in another calculation to return the last cumulative value.

Cumulative values in BCWP

Weeks	BCWP
03-Sep-12	$525
10-Sep-12	$2,800
17-Sep-12	$5,885
24-Sep-12	$7,820
01-Oct-12	$9,725
08-Oct-12	$15,170
15-Oct-12	$20,770
22-Oct-12	$0
29-Oct-12	$0
05-Nov-12	$0
12-Nov-12	$0
19-Nov-12	$0

[Gysbert_Wassenaar]

How about max(BCWP) to get the maximum value? As long as the cumulative keeps growing this should work ok.

[#3]

That definitely makes sense. For some reason it returned null  -

I tried this below, but it ended up returning the same values shown above.

You would think QV would have a function to check for last known value in a column.

Max(aggr(Sum(EV_TBC * %Complete),EVM_Weeks))

[rwunderlich · Accepted solution]

Add TOTAL to your expression.

Max(TOTAL aggr(Sum(EV_TBC * %Complete),EVM_Weeks))

-Rob

http://robwunderlich.com

[#5]

That was it!  I was missing the TOTAL clause. Thanks Rob 

Very helpful site BTW, great tools.

[#6]

What if I have a column that is similar but not cumulative? I.e the values simply vary... Any Quick way to get the last value?

[rlp]

Max(TOTAL aggr( above( TOTAL Sum(EV_TBC * %Complete) ),EVM_Weeks))

[#8]

That works. I see it has above and  then Total used twice. 

Is this just a different way or is it more powerful/flexible ?

[#9]

Its works great on a Cumulative column, but wont on non-cumulative. I don't think its possible.

What Im trying to is find the last value of a Line Expression that is non cumulative.

Granted the Max works in my case sample above but not in every case.  See Marked Line in attachment

Its ueses this Exp which jumps all over the place which is fine but make sit hard to find last value.

=($(BAC)) / ($(CPI))

[Gysbert_Wassenaar]

Maybe just bottom(SomeExpression) ?