One dimension and One Expression, How to return just last row value?

[#1]

Hi Community,

I want the value of the last row in my table chart.  I have one dimension and one expression.  I want to store this value as a variable.  Here are the dimension and expression. 

Dimension:

=if(left(LinkMonth,2)+LinkYear*12<=left(Closed_Period,2)+(Closed_Year+1)*12,LinkMonth&'-'&LinkYear)

Expression:

=rangecount(above((sum({$<Ticker*={'AGCO'}>}Close2)/count({$<Ticker*={'AGCO'}>}Close2)/

above(sum({$<Ticker*={'AGCO'}>}Close2)/count({$<Ticker*={'AGCO'}>}Close2))-1),0,100),above((sum({$<Ticker*={'DE'}>}Close2)/count({$<Ticker*={'DE'}>}Close2)/

above(sum({$<Ticker*={'DE'}>}Close2)/count({$<Ticker*={'DE'}>}Close2))-1),0,100))

[#2]

Hi Brandon, there isn't a direct way to pull values out of a table, but you could set the variable using the function firstsortedvalue(expression, sortweight) nested with an AGGR() statement

In your case the expression would be the expression above, using aggr to group the values by the dimension:

aggr([dimension],[expression])

you would then put this into firstsortedvalue to get somethinge like this:

=firstsortedvalue(aggr([dimension],[expression]),*whatever you are using to sort the table*)

to simulate the last value. However, this might get quite long and cumbersome looking at your formulae above. You may wish to script some of the calculations to a) make it all quicker and b) simpler.,

Regards,

Erica

[#3]

If I add bottom() in front of my expression I get the last value for all the values in the expression.  Using this I don't need to sort, I just need to save any of the values. 

How do I put in the *whatever you are using to sort the table* into to the table.  I could have any sort method and it should work for me but it doesn't give me anything without something to sort on.  Here is my code for the variable below.

=firstsortedvalue(aggr(if(left(LinkMonth,2)+LinkYear*12<=left(Closed_Period,2)+(Closed_Year+1)*12,LinkMonth&'-'&LinkYear),bottom(rangecorrel(above((sum({$<Ticker*=Ticker3>}Close2)/count({$<Ticker*=Ticker3>}Close2)/
above(sum({$<Ticker*=Ticker3>}Close2)/count({$<Ticker*=Ticker3>}Close2))-1),0,100),above((sum({$<Ticker*=Ticker2>}Close2)/count({$<Ticker*=Ticker2>}Close2)/
above(sum({$<Ticker*=Ticker2>}Close2)/count({$<Ticker*=Ticker2>}Close2))-1),0,100))
)))

[#4]

//You could do a vb script, something like this.

Sub ???()

set chart = ActiveDocument.GetSheetObject("????")   'object id

set cell = chart.GetCell(chart.GetRowCount -1, ?)       'collum id

'msgbox(cell)

ActiveDocument.Variables("v???").SetContent cell, true // the variable most be created in qlikview erlier

end sub