Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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