topic Re: How to Find last value in Table using Bottom function in QlikView

How to Find last value in Table using Bottom function

Thu, 04 Oct 2012 13:39:28 GMT

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

Gysbert_Wassenaar — Thu, 04 Oct 2012 14:15:36 GMT

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

Thu, 04 Oct 2012 14:24:50 GMT

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

rwunderlich — Thu, 04 Oct 2012 14:39:06 GMT

Add TOTAL to your expression.

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

-Rob

http://robwunderlich.com

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

Thu, 04 Oct 2012 14:47:37 GMT

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

Thu, 04 Oct 2012 15:43:43 GMT

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

rlp — Fri, 05 Oct 2012 12:42:33 GMT

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

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

Fri, 05 Oct 2012 12:56:21 GMT

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

Fri, 05 Oct 2012 13:03:42 GMT

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

Gysbert_Wassenaar — Fri, 05 Oct 2012 13:07:37 GMT

Maybe just bottom(SomeExpression) ?

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

Fri, 05 Oct 2012 13:19:16 GMT

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

Fri, 05 Oct 2012 13:22:34 GMT

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

rlp — Fri, 05 Oct 2012 13:23:43 GMT

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

rlp — Fri, 05 Oct 2012 14:20:34 GMT

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

Fri, 05 Oct 2012 14:43:30 GMT

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

rlp — Fri, 05 Oct 2012 14:52:40 GMT

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

{ "<=

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

Fri, 05 Oct 2012 14:57:50 GMT

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

Thank you,

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

rlp — Fri, 05 Oct 2012 15:05:46 GMT

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

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

rwunderlich — Fri, 05 Oct 2012 18:19:51 GMT

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

Fri, 05 Oct 2012 18:45:50 GMT

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