Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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" ?
to do this in the script
Please refer to :
and
to achieve this in the front end; please refer to :
I am trying to calculate the difference between "energy in wh" based on two times stamps.
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.