Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Calculate difference between each running timestamp

Hi

I have a table with timestamp 'YYYY-MM-DD HH:mm:ss' and some measurements. 
I would like to load the difference between the measurements from one timestamp to another.

In my example, I just use the dates, as it is faster 🙂 

 

load * inline [

timestamp, measurement
2020-01-01, 10
2020-01-02, 30
2020-01-03, 80
2020-01-04, 110
2020-01-05, 150
2020-01-06, 500
]; 

 

 

What I am looking for is to load a table that now looks like this, where the difference is being calculated between all timestamps chronological. 

timestampmeasurementconsumption 
2020-01-011010
2020-01-023020
2020-01-038050
2020-01-0411030
2020-01-0515040
2020-01-06500350

 

 

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Perhaps like this:

load *, alt(measurement-previous(measurement),measurement) as consumption inline [

timestamp, measurement
2020-01-01, 10
2020-01-02, 30
2020-01-03, 80
2020-01-04, 110
2020-01-05, 150
2020-01-06, 500
]; 

 


talk is cheap, supply exceeds demand
Creator
Creator

OKay, did not think it was that simple 🙂 

What if I wanted to add a field I forgot.  I have the ID were I want to calculate the difference between the two measurements between every date. 

Maybe it would help if I told you it was a heat meter for measurement heat. The values from this heat meter are accumulating, so I want to know what this heat meter uses every day.  

load *
inline [
id,timestamp, measurement
550055,2020-01-01, 10
550055,2020-01-02, 30
550055,2020-01-03, 80
550055,2020-01-04, 110
550055,2020-01-05, 150
550055,2020-01-06, 500
225500,2020-01-01, 8
225500,2020-01-02, 11
225500,2020-01-03, 40
225500,2020-01-04, 90
225500,2020-01-05, 50
225500,2020-01-06, 3
]; 

 

Highlighted
Contributor III
Contributor III

I think, this may solve your problem

load *
,if(peek(id)=id, alt(measurement-previous(measurement),measurement),measurement) as consumption 
inline [
id,timestamp, measurement
550055,2020-01-01, 10
550055,2020-01-02, 30
550055,2020-01-03, 80
550055,2020-01-04, 110
550055,2020-01-05, 150
550055,2020-01-06, 500
225500,2020-01-01, 8
225500,2020-01-02, 11
225500,2020-01-03, 40
225500,2020-01-04, 90
225500,2020-01-05, 50
225500,2020-01-06, 3
];