Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More

Missing Manual - Above() and Below()

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 &amp; 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()

Attachments
Comments
datanibbler
Esteemed Contributor

Yep!

That is one of the really useful things in the book "Mastering QlikView" I'm just reading. However, an example is always helpful.

Using this you can e.g. calculate a rolling average of the last few months in any type of chart (it is possible to just switch the type of chart to "straight table" to visualize just what this function will do and then switch back)

nadeemsmarty
Valued Contributor

Brilliant solved my queries regarding the above function

manish_madan
Contributor

Hi Robert,

For the first example , I am not getting the same output as yours.

when Case is sum( Sales )    -----------> above(sum( Sales )

Output which i am getting is:


above.PNG


It is by default showing result for offset 1.


Could you please help.

Thanks

Manish

0 Likes
manish_madan
Contributor

Hi,

Somehow i am not getting the same output as yours,Attached Screenshot for reference:

abov_below.PNG

Thanks

Manish

0 Likes
pandunallani
Contributor II

Hi manish,

u need to sort the month columnUntitled.png

0 Likes
Not applicable

Just another excellent How To. Thanks robert_mika !

ahaahaaha
Honored Contributor

Thanks for the useful information.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-04-07 10:55 AM
Updated by: