Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

WeeksBCWP
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
1 Solution

Accepted Solutions
rwunderlich

Add TOTAL to your expression.

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

-Rob

http://robwunderlich.com

View solution in original post

19 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Add TOTAL to your expression.

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

-Rob

http://robwunderlich.com

Not applicable
Author

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

Very helpful site BTW, great tools.

Not applicable
Author

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

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

Not applicable
Author

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

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

Not applicable
Author

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


talk is cheap, supply exceeds demand