Finding the Max - Min over the last 5 rows

Good Afternoon Tibco Community.  I am trying to make a calculated column finding the Max() - Min() over the last 5 rows

Example

Rows Days Calculated Column
1 10  
2 20  
3 30  
4 40  
5 50 =Max(Days1:Days5) - Min(Days1:Days5)
6 60 =Max(Days2:Days6) - Min(Days2:Days6)
7 70 =Max(Days3:Days7) - Min(Days3:Days7)
8 80 =Max(Days4:Days8) - Min(Days4:Days8)
9 90 =Max(Days5:Days9) - Min(Days5:Days9)
10 100 =Max(Days6:Days10) - Min(Days6:Days10)
11 110 =Max(Days7:Days11) - Min(Days7:Days11)
12 120 =Max(Days8:Days12) - Min(Days8:Days12)
13 130 =Max(Days9:Days13) - Min(Days9:Days13)
14 140 =Max(Days10:Days14) - Min(Days10:Days14)
15 150 =Max(Days11:Days15) - Min(Days11:Days15)

This is essentially what I am trying to do

I have tried:

( Max([Days])OVER(LastPeriods(5,[Days])) ) - ( Min([Days])OVER (LastPeriods(5,[Days])) )

and it is not coming out at all like the excel sheet

also tried

Max(LastPeriods(5,[Days])) - Min(LastPeriods(5,[Days]))

and receive "could not find LastPeriods on line 1".  I am thinking this one could work if I could figure out how to start this at row 5

 

Please let me know your thoughts Tibco Community b/c I am pretty stumped on this one and new to Over statements.

(1) Answer

Login