Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Add TOTAL to your expression.
Max(TOTAL aggr(Sum(EV_TBC * %Complete),EVM_Weeks))
-Rob
How about max(BCWP) to get the maximum value? As long as the cumulative keeps growing this should work ok.
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))
Add TOTAL to your expression.
Max(TOTAL aggr(Sum(EV_TBC * %Complete),EVM_Weeks))
-Rob
That was it! I was missing the TOTAL clause. Thanks Rob
Very helpful site BTW, great tools.
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?
Max(TOTAL aggr( above( TOTAL Sum(EV_TBC * %Complete) ),EVM_Weeks))
That works. I see it has above and then Total used twice.
Is this just a different way or is it more powerful/flexible ?
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))
Maybe just bottom(SomeExpression) ?