3 Replies Latest reply: Jul 10, 2012 5:29 AM by Gustav Lindström RSS

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

    brandon breitling

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

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

          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

            • Re: One dimension and One Expression, How to return just last row value?
              brandon breitling

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

               

            • Re: One dimension and One Expression, How to return just last row value?
              Gustav Lindström

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