Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

varmekontrol
Contributor

Calculate difference in sum based on previous day, week, month,year

Hi

I need help on how to find the consumption of a heat meter based on the previous date. 

My Data

Meter No]CreatedEnergy (wH)What i need
8225114517-03-2019100 
8225114518-03-2019250150
8225114519-03-2019500250
8225114520-03-20191200700
8225114521-03-201950003800
8225114522-03-201960001000
8225114523-03-201985002500
8225114524-03-201995001000

 

My load.

 

Heat_meter:
LOAD 
Num(@2) as [Meter No], //Heat meter serial no. Date(@3) as [Created], //Timestamp Num(@7) as [Energy (wH)] //Actual energy used since the day it was installed.

//What i need is to subtract the Energy from the previous day, to get the consumption used the last day.
FROM [D:\Elvaco_FTP\Elvaco_Teknikrum\HeatMeter\*.csv] (txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines where Date(@3)>=today()-7;

 

 

 

1 Solution

Accepted Solutions
Partner
Partner

Re: Calculate difference in sum based on previous day, week, month,year

Heat_meter:
LOAD 
     Num(@2) as [Meter No], //Heat meter serial no. 
     Date(@3) as [Created], //Timestamp
     Num(@7) as [Energy (wH)], //Actual energy used since the day it was installed. 
     Num(@7 - peek('@7') as [What i need]

FROM
[D:\Elvaco_FTP\Elvaco_Teknikrum\HeatMeter\*.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines where Date(@3)>=today()-7;
 

Read about peek() here:

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/In...

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

3 Replies
Partner
Partner

Re: Calculate difference in sum based on previous day, week, month,year

Heat_meter:
LOAD 
     Num(@2) as [Meter No], //Heat meter serial no. 
     Date(@3) as [Created], //Timestamp
     Num(@7) as [Energy (wH)], //Actual energy used since the day it was installed. 
     Num(@7 - peek('@7') as [What i need]

FROM
[D:\Elvaco_FTP\Elvaco_Teknikrum\HeatMeter\*.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines where Date(@3)>=today()-7;
 

Read about peek() here: 

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/In...

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Partner
Partner

Re: Calculate difference in sum based on previous day, week, month,year

Heat_meter:
LOAD 
     Num(@2) as [Meter No], //Heat meter serial no. 
     Date(@3) as [Created], //Timestamp
     Num(@7) as [Energy (wH)], //Actual energy used since the day it was installed. 
     Num(@7 - peek('@7') as [What i need]

FROM
[D:\Elvaco_FTP\Elvaco_Teknikrum\HeatMeter\*.csv]
(txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines where Date(@3)>=today()-7;
 

Read about peek() here:

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/In...

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

varmekontrol
Contributor

Re: Calculate difference in sum based on previous day, week, month,year

(Jeg siger tak for hjælpen, det var lige det jeg skulle bruge. :))

Just a msg. for my Scandinavian buddy 🙂
The Peek() was just what i needed. 

This is my result if someone else needs the solution. 

Heat_meter_temp:
LOAD Cme_no, 
     Meter_no, 
     date(Created, 'YYYY-MM-DD HH:MM:ss') as Created,
     num(Energy_wH/1000) as Energy_wH, 
     Volume_m3, 
     Volume_flow_m3H, 
     Flow_temp_C, 
     Return_temp_C, 
     Temp_diff_C
FROM
[C:\Heat_meter.qvd]
(qvd);

Heat_meter: 
NoConcatenate
LOAD *,
	 peek('Energy_wH') as Energy_wH_peek,
	 ((Energy_wH-peek('Energy_wH'))) as Energy_wH_consumption,
	 peek('Volume_m3') as Volume_m3_peek,
	 num(((Volume_m3-peek('Volume_m3'))),'##,00') as Volume_m3_consumption
Resident 
Heat_meter_temp order by Meter_no,Created;

Drop Field Energy_wH_peek, Volume_m3_peek;
Drop Table Heat_meter_temp;