Peek doesn't works

[reporting_neu]

Hi,

I would like to calculate a consumption.

For this, the line around the previous line must be subtracted.

To do this, I first loaded the data in order to then calculate the consumption.

NoConcatenate
TMP1:
LOAD
    Date(created) as created,
    Date(date#([ReferencePeriod],'MM/YYYY'), 'MM/YYYY') as ReferencePeriod,
    Number,
    MeterReading,
    Year(Date(date#([ReferencePeriod],'MM/YYYY'), 'YYYY')) as NYear,
    Month(Date(date#([ReferencePeriod],'MM/YYYY'), 'MM')) as NMonth
FROM [lib://FICILITY/MeterReading.xlsx]
(ooxml, embedded labels, table is Fernwaerme);


Qualify '*';

NoConcatenate
Fernwaerme:
Load * 
,   if(Number <> Peek(Number), MeterReading, MeterReading- Peek(MeterReading)) as Consumption
Resident TMP1
Order By Number, created, ReferencePeriodDESC;

Unqualify '*';

Drop Table TMP1;

The "Number" don't match for me even though they are identical. Doesn't matter if I use "upper(text())".

But only "MeterReading- Peek(MeterReading)) as Consumption" doesn't works, too.

What is wrong???

[Chanty4u · Accepted solution]

Instead of peek have tried previous?

NoConcatenate

Fernwaerme:

Load * 

, if(Number <> Previous(Number), MeterReading, MeterReading - Previous(MeterReading)) as Consumption

Resident TMP1

Order By Number, created, ReferencePeriod DESC;

[reporting_neu]

With "previous" it works! Thanks very much!