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

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

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