Peek with group of two rows

[lanlizgu]

I am trying to use the peek function to sustract the previous value without success.

What I would like to do is obtain the value from the expected value column. However, for the third line I am getting a 1 (6-5) instead of getting the first value of the second Field 1=2, as i understand that I am missing an aggregation by Field1 or something like that

Field1	Date	Value	ExpectedValue
1	01/01/2017	3	3
1	01/01/2018	5	2
2	01/01/2017	6	6
2	01/01/2018	9	3

The query I am using

LOAD

Field1

Date

Value-Peek(Value)) as ExpectedValue,

resident MyTable

order by Field1  asc

;

[m_woolf]

I didn't test this:

LOAD

Field1

Date

if(Field1 = peek('Field1'),Value-Peek('Value'),Value) as ExpectedValue,

resident MyTable

order by Field1  asc,Date asc

[Chanty4u]

may be

1. left join(A) 
2. LOAD 
3.     Date, 
4.     Filed1, 
5.     if([Field1] = Previous([Field1]), [Value] - Previous([Value])) as Exp 
6. Resident 
7.     A 
8. Order by Date, Field1; 

[jaumecf23 · Accepted solution]

Hi,

Try this:

Table1:

Load*,

if(peek(Field1)<>Field1,Value,Value-peek(Value)) as ExpectedValue

;

LOAD * inline [

Field1, Date, Value

1, 01/01/2017, 3

1, 01/01/2018, 5

2, 01/01/2017, 6

2, 01/01/2018, 9

];

[lanlizgu]

Thank you Jaume, working correctly.