Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Highlighted
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
];