Missing Manual - Above() and Below()

    1f4fe19.jpgHave you ever wondered how the examples from the Qlikview help may look like?

    Please see below and enjoy responsibly...

     

    Below() and Above()

     

    07-Apr-15 3-22-43 PM.jpg

     

     

     

    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 or, in the case of bitmap charts, in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure.).

    On the first row of a column segment a NULL value will be returned, as there is no row above this one.

    If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column........

     

     

    • Returns value from  previous (Above) ornext (Below) row(s)
    • Returns NULL for the first or last row.
    • Equivalent to before()and after()for columns in Pivot Table.


    Syntax:

    08-Apr-15 10-59-40 AM.jpg

    08-Apr-15 11-04-32 AM.jpg

    TOTAL[<fld{,fld}>]]expr - expression

    offset - if greater that 0 will move the evaluation of expression to rows

    further down or above

    count - this parameter will only works with Chart Range Function (like RangeSum), it will specify the numbers of rows to be taken for calculations.(Please see last example)


    Data Model:

    (Copy and Pasted below code into Edit Script window and reload)

     

    LOAD * inline

    [

    Year ,Month ,Sales

    2015 ,January, 10

    2015, February,20

    2015 ,March ,30

    2014 ,January ,10

    2014 ,February,20

    2014 ,March ,30

    2013 ,January ,10

    2013 ,February ,20

    2013 ,March,30

    ]

     

    Example 1:


    Let's build a Straight Table with Year and Month as dimensions and expressions as below:

     

    The left hand side shows use of sum(Sales) the right hand side result from our new expression.

     

                    sum( Sales )    -----------> above(sum( Sales ) )

    07-Apr-15 4-09-46 PM.jpg

    In each groups the first value is now NULL,the last values(30) have been omitted and the rest of the rows have been assigned value from one row below current row.

                    sum( Sales )    ----------->  below(sum( Sales ) )

    07-Apr-15 4-08-34 PM.jpg


    In each groups the last value is now NULL,the first values (10) in each group have been omitted and the rest of the rows have been assigned value from one row below current row.

     

     

    Example 2


    By specifying the second criteria as 2 ,values in each group are shifted two rows up or down

     

              sum( Sales )-----------> below sum( Sales ), 2 )

    08-Apr-15 11-28-03 AM.jpg

     

              sum( Sales )-----------> above( sum( Sales ), 2 )

    08-Apr-15 11-34-16 AM.jpg

     

    Example 3


            above(TOTAL sum( Sales ) )      below(TOTAL sum( Sales ) )

     

    08-Apr-15 12-18-13 PM.jpg

    Adding TOTAL before Sum will result with the first or last value to be omitted and the calculation to be shifted one row down or up.

     

     

    Example 4


    rangeavg (Above(sum(Sales),1,2))

    rangeavg (Below(sum(Sales),1,2))

     

    RangeAvg() takes 3 parameters

    -expression ---> Above/Below(sum(Sales),

    -offset of rows--->1

    -number of rows to sum--->2

                                                                 

                                                       sum(Sales)                         rangeavg (Above(sum(Sales),1,2))

    08-Apr-15 12-37-49 PM.jpg

    For each group in right table:

    • First row is NULL
    • Rows 2 and 3 are averages of values from two rows above current one (first we are going 1 row above and then adding 2 rows)

     

     

          sum(Sales)                          rangeavg (Below(sum(Sales),1,2))

    08-Apr-15 1-01-07 PM.jpg


                     

    For each group in right table:

    • Rows 1 and 2 are averages of values from two rows below current one (first we are going 1 row above and then adding 2 rows)
    • Last row is NULL

     

    Still feeling hungry?

     

    How to use - Only()

    Do you Qualify?- How to use QUALIFY statement

    How to use - Dimensionality()

    Missing Manual - GetFieldSelections() + Bonus Example

    MaxString & MinString - How to + examples

    The second dimension... or how to use secondarydimensionality()

    Missing Manual - Below() and Above()

    How to use- Dual()

    Missing Manual - Before() and After()

    How to use - MissingCount()