2 Replies Latest reply: Aug 31, 2011 5:18 AM by Andreas Dippel RSS

    using line() in formula instead of column()

      i was making some calculations in pivot tables.

      while doing so, i needed to subtract values from 2 fields, which are in the !same column. so column(?) does not work.

       

      usually, you can use "column(2)-column(1)" in formulas. this subtracks fields in collum 2 from a flied in column 1 (each in the same row)

       

      now i want to subtract 2 fields in the same column, but the fields are in different lines. like: field(col1:line7)-field(col1:line6)

       

      how can i adress thoose fields (like in excel "(B17-18)") ?

       

      any suggestions ?

      thanks

      alex

        • using line() or row() like column()
          Stefan Wühl

          Interesting, a row() seems to be missing.

           

          What could work is using chart inter record function top(), like

          top(YOUREXPRESSION,N)

           

          to evaluate your expression in the context of Nth row. You probably need a conditional expression, but for example, this worked in a total row (checking for rowno()=0) like =if(rowno()=0,top(sum(Sales),1)-top(sum(Sales),2))

          to subtract sum Sales in context of row 1 - row2.

           

          Stefan

            • Re: using line() in formula instead of column()

              for formulas, which only need to use 1 row, its seems to be sufficient, to assign a row by using

              top(coloumn(2),1) for the first cell in coloumn 2.

               

              but if its necessary to use the formula with more than one row, we need a "row()" function.

              or we use the suggestioon by "swuehl":  (coloumn(2),n) where we get "n" by counting rows.

               

              this could be made much simpler, and less prone to failures with an introduction of "row()" in qlickview 10