4 Replies Latest reply: May 18, 2018 5:18 AM by youssef belloum

# Calculate measure between two dates (Consumption)

Hello

I can´t seem to find an answer to this question, so hoping someone out there is able to help me with it.

I am working for a utility and billing company and am using Qlik for consumption and monitoring of Heat, Water, Electricity etc.

Because of this, I am interested in - Consumption.

# So if I have this type of data.

timestamp energy in Wh 15-05-2018 00:00 36940130 15-05-2018 01:00 36940170 15-05-2018 02:00 36940210 15-05-2018 03:00 36940250 15-05-2018 04:00 36940290

I would like two things.

1: How do I calculate the difference between two timestamps. (To use in a chart)

Timestamp 15-05-2018 01:00 (36940170 Wh)

Minus

Timestamp 15-05-2018 00:00 (36940130 Wh)

=  40 Wh

2: To make the same calculation in the scrip.

latest timestamp ("Electricity(Wh)") - the timestamp before this  ("Electricity(Wh)") AS  Consumption

Valuereport:
LOAD
@2 as meter_no,
@3 as timestamp,
,
@5 as "Electricity(Wh)"
FROM [lib://consumptionreports/*]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines);

• ###### Re: Calculate measure between two dates (Consumption)

Hi,

in the first image, you have two fields: "timestamp", "energy in wh"

from which field you're trying to calculate the consumption?  Timestamp-timestamp ? or "energy in wh"-"energy in wh" ?

• ###### Re: Calculate measure between two dates (Consumption)

I am trying to calculate the difference between "energy in wh"  based on two times stamps.

• ###### Re: Calculate measure between two dates (Consumption)

If you have data like this (2 columns):

timestamp energy in Wh 15-05-2018 00:00 36940130 15-05-2018 01:00 36940170 15-05-2018 02:00 36940210 15-05-2018 03:00 36940250 15-05-2018 04:00 36940290

1.     if you're looking to get 40kw between the first two lines, do this on the script:

table:

load

.

"energy in wh"-Previous("energy in wh") as Consumption

.

FROM...

2.     if you're trying to calculate the difference between two timestamps and get the consumption using other calculations, do this:

table:

load

.

interval(Timestamp#(timestamp,'DD-MM-YYYY hh:mm')-Timestamp#(timestamp,'DD-MM-YYYY hh:mm'),'hh:mm') as timestamp_difference.

FROM...

all this are Back-end (script) solutions, there is front-end solutions using Above() and interval() functions.