Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

varmekontrol
New Contributor II

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.

timestampenergy in Wh
15-05-2018 00:0036940130
15-05-2018 01:0036940170
15-05-2018 02:0036940210
15-05-2018 03:0036940250
15-05-2018 04:0036940290

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);

4 Replies
YoussefBelloum
Esteemed Contributor

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" ?

OmarBenSalem
Esteemed Contributor

Re: Calculate measure between two dates (Consumption)

to do this in the script

Please refer to :

QlikView Peek Function

and

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/InterRecordFun...

to achieve this in the front end; please refer to :

The Above Function

varmekontrol
New Contributor II

Re: Calculate measure between two dates (Consumption)

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

YoussefBelloum
Esteemed Contributor

Re: Calculate measure between two dates (Consumption)

If you have data like this (2 columns):

timestampenergy in Wh
15-05-2018 00:0036940130
15-05-2018 01:0036940170
15-05-2018 02:0036940210
15-05-2018 03:0036940250
15-05-2018 04:0036940290

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.

Community Browser