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

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

Wed, 13 May 2009 23:59:22 GMT

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

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

yblake — Thu, 14 May 2009 00:05:21 GMT

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

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

Thu, 14 May 2009 00:21:51 GMT

Hi yves,

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

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

johnw — Thu, 14 May 2009 01:12:59 GMT

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?

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

Thu, 14 May 2009 23:42:47 GMT

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.

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

johnw — Fri, 15 May 2009 01:26:23 GMT

How about this?

=(CPvCCC=above(CPvCCC))

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

Sat, 16 May 2009 02:13:56 GMT

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

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

johnw — Sat, 16 May 2009 02:33:17 GMT

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

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

Sat, 16 May 2009 05:49:13 GMT

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.

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

johnw — Mon, 18 May 2009 22:40:53 GMT

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.

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

Sat, 23 May 2009 05:19:14 GMT

Thanks for the tip. I finally got this resolved by creating a resident table using the previous function:

INNER JOIN
LOAD
CPvCCC AS CPvCCCx,
Daily_Production_Schedule.Production_Run_ID,
if(CPvCCC=previous(CPvCCC), '0', '1') as CPvCCCChange
RESIDENT DailyProduction;

I used the previous function to create a value of 1 if the change accorded. Then I inner joined back to the main table on Production Run ID, and just simply summed up all the "1"s to be found. The comparison of records within dimension groups was confusing, but once I understand that, I was able to come up with the resident table work around using the "previous" function. The key was to limit my data set down to two columns... Thanks for the help.