Wednesday, August 20, 2014

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.




2 comments:

  1. In your first example, to filter out any record column // sum (aggr (rangesum (above (full on)), sno)) is set to 0, example:

    Registration 2 column (sno) possessed a value of 20 in the column // sum (aggr (rangesum (above (full on)), sno)), however, to filter the log 2 value changes to 0 to invez to stay the value 20.

    ReplyDelete
  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh Srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+91 8553576305 / 080 - 41103383
    http://www.maxmunus.com/


    ReplyDelete

web stats