19 Replies Latest reply: Oct 5, 2012 2:45 PM by Brian Garside

# How to Find last value in Table using Bottom function

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
• ###### Re: How to Find last value in Table using Bottom function

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

• ###### Re: How to Find last value in Table using Bottom function

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

• ###### Re: How to Find last value in Table using Bottom function

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

-Rob

http://robwunderlich.com

• ###### Re: How to Find last value in Table using Bottom function

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

Very helpful site BTW, great tools.

• ###### Re: How to Find last value in Table using Bottom function

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?

• ###### Re: How to Find last value in Table using Bottom function

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

• ###### Re: How to Find last value in Table using Bottom function

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

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

• ###### Re: How to Find last value in Table using Bottom function

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

• ###### Re: How to Find last value in Table using Bottom function

Maybe just bottom(SomeExpression) ?

• ###### Re: How to Find last value in Table using Bottom function

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
• ###### Re: How to Find last value in Table using Bottom function

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

Thanks.

• ###### Re: How to Find last value in Table using Bottom function

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

• ###### Re: How to Find last value in Table using Bottom function

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

• ###### Re: How to Find last value in Table using Bottom function

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

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

• ###### Re: How to Find last value in Table using Bottom function

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.

• ###### Re: How to Find last value in Table using Bottom function

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.

• ###### Re: How to Find last value in Table using Bottom function

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

{ "<=

• ###### Re: How to Find last value in Table using Bottom function

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

• ###### Re: How to Find last value in Table using Bottom function

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