Help using the "above" function in a pivot object.

[#1]

I am trying to return a value based on a previous record in a pivot, but the "above: function does not seem to be working for me. Attached is a picture of what I am trying to do. Any ideas? Thanks! - Mike

[yblake]

Hi,

Try this

if(above(CPvCCC)<>'',true(),false())

Not that above just pick previous row, not previous column (for this you need to use before).

[#3]

Hi yves,

I tried the above syntax, but it just returned all "True" values for each row. Any other suggestions? Thanks for the help.

[johnw]

What do you WANT to see happen in the Test column? Because my assumption was the same as yves. Since all CPvCCC values are filled in, it looks like you want true on every row but the first. Apparently that isn't what you're after. What ARE you after?

[#5]

I should have clarified. There is a column shown in the image in the first post named "CPvCCC Case Pack & Commodity Class Code (Packaging)

", this is the CPvCCC value… If the previous record for CPVCCC is the not the same as the current record for that column, then my expression should return False. Thanks.

[johnw]

How about this?

=(CPvCCC=above(CPvCCC))

[#7]

That didn't work either. Zeroes are returned on all rows.

[johnw]

Works for me. See attached. You may need to post an example if you're still having trouble.

[#9]

Thanks John. I downloaded your example, I am using the same syntax as you, but I am still getting zeroes. In your example, I slid the CPvCCC column to the right as the last dimesion of the pivot, and it returned zeroes like mine, yet it should still be returning -1 and 0s because some of the "above" records are different. Please see attached image.

[johnw · Accepted solution]

You'll always get zeroes if CPvCCC is the rightmost field. QlikView isn't literally just checking the value above the current row. From the help text, "Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within a column segment in a table..." The key point here is the mention of "column segment". If I remember correctly the column segment is ONLY that portion of the column that has the same values for all fields to the left. By making CPvCCC the rightmost field, you guaranted that either it is a different value in the same column segment, the same value in a different column segment, or a different value in a different column segment. All of these cases will return zero.

And, now that I'm looking more closely at your data, that IS the reason my expression wasn't working on your original data. Your fields to the left of CPvCCC appear to be unique values, which pretty much prevents above() from ever working. More accurately, I believe it will always return null on your chart. Sorry about that.

I'll let you know if I think of any approach that might work. Surely it's solvable, but right now, I'm not seeing the solution.