( based on a true story)
Return Average of last 3 months(not including current one)
(Click on picture to see full size)
Create Straight Table (Layout-->New Sheet Object-->Chart...) -->
Year and Month
as dimension -->
and Sales as expression.
(In this example Sales and SUM(Sales) deliver the same results as we have unique values in Month dimension. -->
The expression we are going to take apart is:
Step 1) SUM()
(We not going to investigate that now so please refer to Qlikview Help for more details)
For our example we only need:
The result of this expression is:
Step 2) ABOVE()
ABOVE takes 3 parameters:
expression - which in our example is: SUM(Sales).(we are ignoring the total here)
offset- this is integer value(0,1,2...) to specify how many rows the whole expression should be move down in relation to current row
n - this is integer value(1,2...) and will return range of values.
The last parameter will only work when used as argument with another Chart Range Function
(please see Qlikview Help for details)
The result of wrapping SUM with ABOVE
is presented below.
Offset of 1,2 and 5 rows:
Step 3) Rangeavg()
This function returns average of values or expressions:
RangeAvg(sum(1+2),sum(2+3)) is equal to RangeAvg(3,5)=4
So what actually is going on behind the scene we we use:
We can rephrase that expression to:
" Sum values from dimension Sales then go 1 row above and take values from 3 rows going up"
So based on our example :
Will always return NULL
as we can not got above row 1 -->
We can only go 1 row up and return 1 value
Rangeavg(2000) = 2000
"Go up 1 and return 3 values going up"
We can only go 2 rows up:
Rangeavg (2000,3000) =
"Go up 1 row..."
This is first row where values from 3 rows above
can be returned(Row 1, Row 2 ,Row 3)
Rangeavg(Row 1, Row 2 ,Row 3) = Rangeavg(2000+3000+1000)=6000/3=2000
Rangeavg(Row 2, Row 3 ,Row 4)
and so on...
The final result:
The offset parameter of the ABOVE function according to Qlikview Help:
Still feeling hungry?