Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
anugangwar
New Contributor

Averaging data between two time periods for multiple dates

Hi All,

I got stuck under a peculiar situation when I am trying create a dashboard. I have the following data sample with me (also attached in the excel file with c. 200K rows data). Now, the "ROLLING SYSTEM" data  is the primary key here and the "Time" field is derived from 'ROLLING SYSTEM' field. One catch here is that time is not consistent with 5 minutes interval (although it is there for most of the times, but I can't make any exceptions as every day new data will be appended).

   

ROLLING SYSTEM TimeDEMAND
2015071500000000:00:0024549
2015071500050000:05:0024570
2015071500200000:20:0024078
2015071500250000:25:0023968
2015071500300000:30:0023924
2015071500350000:35:0023985
2015071500450000:45:0023958
2015071500500000:50:0023830
2015071500550000:55:0023468
2015071501000001:00:0023356
2015071501200001:20:0023134
2015071501250001:25:0023025
2015071501300001:30:0022912
2015071501350001:35:0022900
2015071501400001:40:0022829
2015071501450001:45:0022820
2015071501500001:50:0022801
2015071501550001:55:0022709

What I would like to have in the output is average demand for every hourly period against the ending hour time period.

i.e. against 01:00:00 the average demand should be average of 'Demand' field data for time period >= 00:00:00 and <01:00:00;

against 02:00:00 the average demand should be average of 'Demand' field data for time period >= 01:00:00 and <02:00:00; and so on. Something like this:

  

Time PeriodAverage Demand
01:00:0024036.6667
02:00:0022942.8889
03:00:0022548.0000
04:00:0022393.5000

It would be great if anyone can help me out with this. thanks a ton in advance. best regards, Anand

Tags (1)
6 Replies
techvarun
Valued Contributor II

Re: Averaging data between two time periods for multiple dates

Hi,

Try the attached

mbrdenmark
New Contributor II

Re: Averaging data between two time periods for multiple dates

Hi Anand

You could try using the hour() function. hour ‒ QlikView

Either you create a calculated dimension in the object or a new field in your dimensiontabel, and add 1 to the hour value so 00:05:00 and 00:55:00 becomes "1" etc.

An alternative solution and if you want a better label, you could try to round your timestamp by the following solution based on hour and string manipulation using:

Time:

LOAD * INLINE [

    Time

    00:05:00

    00:55:00

    01:05:00

];

TimeRound:

Load *,

time(hour([Time]) +1 & ':00:' & '00') as RoundTime

Resident Time;

Else look into the following thread for other ways to round a timestamp to whole hours.

Rounding timestamp to hour

anugangwar
New Contributor

Re: Averaging data between two time periods for multiple dates

Many thanks Varun for your reply. I guess I missed one point here. The average demand needs to be calculated for every hour for each time period for all the dates given (dated are given in the first column of attached excel 'ROLLING SYSTEM' field, where the first eight characters gives us date in 'YYYYMMDD' format. The output should be something like:

   

ROLLING SYSTEM Time PeriodAverage Demand
201507150000001:00:0024036.6667
201507150000002:00:0022942.8889
201507150000003:00:0022548
201507150000004:00:0022393.5
201507160000001:00:0025554.2
201507160000002:00:0025457.1

Many thanks in advance for your help here. Best regards, Anand

techvarun
Valued Contributor II

Re: Averaging data between two time periods for multiple dates

Hi Anand,

Please find the attached updated qvw

antoniotiman
Honored Contributor III

Re: Averaging data between two time periods for multiple dates

Hi Anand,

see Attachment.

Regards,

Antonio.

Re: Averaging data between two time periods for multiple dates

may be this

Data:

LOAD [ROLLING SYSTEM],

    date(date#(left([ROLLING SYSTEM],8),'YYYYMMDD'),'YYYYMMDD') as Date,

    time#(Time,'hh:mm:ss') as Time,

    DEMAND

FROM

[Final Demand Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

New:

NoConcatenate

LOAD Time,

    Date&'000000' as [ROLLING SYSTEM],

    Time(Time + maketime(1,0,0),'hh')&':00:00' as [Time Period],

    DEMAND

Resident Data;

DROP Table Data;

Capture.JPG