ABOVE Function

[#1]

Hi community,

Please i want an example of QVW application to use the following function : above( [ total ] expression [, offset [,n ]] )

Also, can someone explain me this function...

Thanks

[#2]

Above simply takes a value from the row Above the current row. It can be used to calculate an accumulating total.

Here are some quick examples.

Above(Column(1))

Above(TOTAL Column(1)) + If(Above(TOTAL Column(3)) > 0, Above(TOTAL Column(3)), 0)

[#3]

Please NMiller, can you explain theses above examples

[#4]

The attached application shows the results, which are probably more helpful than an explanation.

The first one can be used with a simple expression in column 1. In the example Sum(Sales). This expression will display the Sum(Sales) for the record above. The Sum of Sales on line 1 is displayed in column 2 in the second row. The Above function resets at each change of the first dimension, which is why you see the nulls (there is nothing Above the first record on a dimension).

Let me change the second one to:

Column(1) + If(Above(TOTAL Column(3)) > 0, Above(TOTAL Column(3)), 0)

The second one is an accumulated total. Column 1 and 2 are the same from above. This expression must be placed in column 3. This takes the value in column 1 (the Sum of Sales for the record) and adds it to the accumulated total above. The If eliminates the nulls as if there is no value Above, it will use zero. The TOTALs are required to not reset at the dimension change.

I don't know how useful either of those would be in an actual application, but they should demonstrate the function pretty well.

Here is an image that helps with the second expression. The value in green is made up of the two values in red.

[#5]

Thanks NMILLER, i saw not the application, i have heard very well

Thank you verymutz

[Anonymous]

Hi NMiller

Thanks a lot for the exmaples you showed. I want exactly same functionali but with partial sum capability. If I turn on the Partial sum in the example you showed. it doesnt work... Can you pease help. I amattached the example you showed with partial sum on.

[#7]

Yes, since there is no Above for the Partial Sum, the expression doesn't work. You can define the Partial Sums equation separately. You use Dimensionality() for that. Dimensionality() tells where in the dimension hierarchy you are. It returns zero for the Partial Sum on the first dimension and goes up from there.

Try:

If(Dimensionality()>1,
 Above(TOTAL Column(1)) + If(Above(TOTAL Column(3)) > 0, Above(TOTAL Column(3)), 0),
 Sum(Sales))

Using this expression, the Partial Sum will be the Sum(Sales), while the detail level will use the Above logic. That may not be exactly what you want. You'll need to come up with an expression that works on the Partial Sum level without using the Above() function.

[#8]

hi NMiler, do you have an example qvw on the Dimensionalty() feature?

I have the same problem, im creating a scorecard wthat compares values from previous row (week) and gives a trend arrrow.  The problme is when a user selects a particular week form a list box the above function doesnt work because there is no above only the current.

regards

[#9]

Hi Neil,

Could you explain a bit more what you mean by "The Above function resets on each change of the first dimension"?

I have transactions that have both Transaction Number (TNBR) and Line Number (LNBR). Sometimes, duplicate line numbers get processed for one Transaction Number. Our client wants that to display as:

TNBR    LNBR

22          8

23          7

23          S/L7   <- this is the duplicated line 7

24          2

25         3    etc.

I tried using as the expression for LNBR:

If(TNBR=Above(TNBR,1) AND LNBR=Above(LNBR,1),'S/L'&Linenumber,Linenumber)

I thought this would give me the "S/L" lines when the TNBR on two consecutive lines was equal, and the LNBR was equal. But  I'm getting "S/L7" on both lines, not just the bottom one. Is this happening because the Above function hasn't "reset" since the TNBR didn't change?

[#10]

Very beautifully explained Neil Miller