6 Replies Latest reply: Aug 10, 2017 8:35 AM by kushal chawda RSS

    Averaging data between two time periods for multiple dates

    Anand Gangwar

      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