QV12 Above() Below() function in qlikview

above and below lie in the Chart Inter Record Functions category. These functions can only be used in chart expressions.

SYNTAX is:
above([ total ] expression [ , offset [,n ]])

It return the value from expression, which is previously seen in the execution.
In other words it return the value the row appear above the current row in table chart.

where we can use above function
- need to create pivot table [accumalation of pivot table]
- need to get the difference and addition of sales (diff. according to month year any perspective) i.e. accumalative sum
Below is the example with some sample data and we will see the behaviour of above() function in straigth table as well as in pivot table.

ex:
LOAD * INLINE [
sno, no
1,20
2,578
3,344
4,54
5,34
6,34
7,34
8,34
];

In chart properties add the below expressions:
- sum(no)
- above(no) ~~ above(no,1)
- below(no,1)
- sum(aggr(rangesum(above(total no)),sno))

Reload the document and see the behaviour of table chart.

image



picturization:
1. sum(no) - will give total sumup of expression
2. above(no) ~~ above(no,1)
as you can see i have written above(no) ,above(no,1) two different entities and but working is same for both. If we skip the offset by default qlikview assume 1.
And see the image above the behaviour is as it start write value from 2 row, by skipping 1, because offset number is one. If we replace it with two it will start writing from 3rd row.

- and you can see the first row having null value, because it there is no value above 20.
3. below(no,1) - It start writing in the column the value below in current expression.

Now we can see the image that the column above(no) and below(no) not summing up the column.
4. to get the sum for the column we need to do "rangesum" with "aggr()" function.



Above() behaviour with pivot table:
script the below inline table

abve:
LOAD * INLINE [
Customer, FK, Sales
A, 1, 50
A, 2, 45
A, 3, 25
A, 4, 75
B, 1, 35
B, 2, 75
B, 3, 12
B, 4, 14
C, 1, 12
C, 2, 50
C, 3, 45
C, 4, 65
D, 5, 45
D, 6, 76
D, 7, 12
E, 1, 12
E, 2, 15
F, 1, 25
F, 2, 56
F, 3, 87
F, 4, 98
F, 5, 54
G, 1, 12
G, 2, 34
G, 3, 15
H, 1, 15
H, 2, 65
H, 3, 15
H, 4, 12
];


LOAD * INLINE [
TestVal
1
2
5
6
7
];

Dimension: - Customers, FK
Expressions:
- Sum (Sales)
pivot_column - =above( column(1))
total_sum - =Above(TOTAL Column(1)) + If(Above(TOTAL Column(3)) > 0, Above(TOTAL Column(3)), 0)

sum customer wise - rangesum(ABOVE(column(1), 1, ROWNO()))

reload and create a pivot table chart.