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

[#2]

The EAC column matches my Line. Problem is its not the Max value in this case. $17,593

I just tried  Bottom( TOTAL ($(BAC)) / ($(CPI))  )  no luck, but do I get what your saying.

I wonder if its just a simple thing I missing or if its really complex...

This one works perfect as is: =($(BAC)) / ($(CPI))

Thanks gwassenaar for the help so far.

Line Exp            NULL                 Last Total Correct for this

EAC	EAC Last	Max(TOTAL aggr(Sum(EV_TBC * %Complete),EVM_Weeks))
$38,400	-	20770
$17,550	-	20770
$19,483	-	20770
$21,107	-	20770
$27,986	-	20770
$22,592	-	20770
$17,593	-	20770
-	-	20770
-	-	20770
-	-	20770
-	-	20770
-	-	20770

[#3]

Ok, thsi works but it requires me to manually put in the last Line using offeset ?

Bottom( TOTAL ($(BAC)) / ($(CPI)), -7  )

[rlp]

Could you post a sample qvw so that we can test before posting ?

Thanks.

[rlp]

I think the simple way is to keep the numeric value of your date EV_WEEKS so that you can do Set Analysis on this.

Moreover, it's always a good thing to keep numeric fields because they are easier to sort.

You can so calculate the cumulative with the following set modifier

          { $<EV_WEEKS = {""<= Only(EV_WEEKS)"}> }

The following step is to use this modifier inside the "max" aggregation

max( TOTAL <the_preceding_set_modifier> <your_expression> )

Hope that helps.

[#6]

Ok, I used it like this but I get a syntax error I marked in yellow. Im new with SA:

Max(TOTAL {$<EV_WEEKS = {""<= Only(EV_WEEKS)"}> } aggr(Sum(EV_TBC * %Complete),EVM_Weeks))

Thanks Richard.

[rlp]

It's an error of mine. There should be only one quote:

{ "<=

[#8]

Ok, that fixed it. Still not sure how SA helps? does it limit it to only the numeric Dates? forgive my ignorance.

Thank you,

BG

[rlp]

In fact Set Analysis provide a way to calculate an accumulation because you change the curerent selection operated by the chart in a selection providing all the dates BEFORE the current date. That's why I said you to keep the numeric value of the date because this enables sorting and so the calculation of the accumulation.

If you try such a sorting with a textual field, you will obtain erroneous results because you would calculate the accumulation over all the weeks whose textual representation is alphabetically before the current selection (e.g. 03-Oct would precede 03-Sep).

[rwunderlich]

I thought the next question might be how to get the last value if it's not max

Here's an expression to return the last non-zero value in Week order.

=FirstSortedValue(TOTAL

aggr(sum(EV_TBC * [%Complete]), Week)

,-if([%Complete]=0,0,Week))

-Rob

http://robwunderlich.com

[#11]

Thanks Rob, I will give that a try. Seems like the right solution.