Above Function Problem

[#1]

Hi,

I use Above Function because I need to do difference between row 1 vs row 2, row 2 vs row 3 etc.

I have this situation:

Item Code	Data	Stock	Data2	Days
B0000051	2011-08-05	36	-	-
B0000051	2011-08-20	30	2011-08-05	15
B0000051	2011-08-24	18	2011-08-20	4
B0000051	2011-09-02	36	2011-08-24	9
B0000051	2011-09-16	30	2011-09-02	14
B0000051	2011-09-24	24	2011-09-16	8
B0000051	2011-09-30	18	2011-09-24	6
B0000051	2011-10-09	12	2011-09-30	9
B0000051	2011-10-14	36	2011-10-09	5
B0000051	2011-11-06	30	2011-10-14	23
B0000051	2011-11-22	30	2011-11-06	16
B0000051	2011-12-17	18	2011-11-22	25
B0000051	2011-12-27	12	2011-12-17	10
B0000051	2012-02-09	18	2011-12-27	44
B0000051	2012-05-18	12	2012-02-09	99
B0000051	9999-12-31	0	2012-05-18	0

I need to have in the first row this scenario:

B0000051

2011-08-05

2011-08-20

15

Etc.

How can I do?

Thanks

[marcus_sommer · Accepted solution]

It seems that you need the inversion from above() the function below().

- Marcus

[Ralf-Narfeldt]

If you have that table loaded, let's say it's called Table1, you can just add:

Table2:

LOAD [Item Code], Data2, Data, Days Resident Table1 Where Not IsNull(Days);

Drop table Table1;

[tresesco]

May be like:

RangeSum(-Data,Below(Data2))

[MK_QSL]

Temp:
Load
  [Item Code],
  Date(Date#(Data,'YYYY-MM-DD')) as Data,
  Stock
Inline
[
  Item Code, Data, Stock
  B0000051, 2011-08-05, 36
  B0000051, 2011-08-20, 30
  B0000051, 2011-08-24, 18
  B0000051, 2011-09-02, 36
  B0000051, 2011-09-16, 30
  B0000051, 2011-09-24, 24
  B0000051, 2011-09-30, 18
  B0000051, 2011-10-09, 12
  B0000051, 2011-10-14, 36
  B0000051, 2011-11-06, 30
  B0000051, 2011-11-22, 30
  B0000051, 2011-12-17, 18
  B0000051, 2011-12-27, 12
  B0000051, 2012-02-09, 18
  B0000051, 2012-05-18, 12
  B0000051, 9999-12-31, 0
];


Final:
Load 
  [Item Code],
  Data,
  Date(Previous(Data)) as PreviousData,
  Interval(Data - Previous(Data),'DD') as Days,
  Stock
Resident Temp
Order By [Item Code], Data;


Drop Table Temp;