2 Replies Latest reply on Jun 2, 2016 11:39 AM by Olga Yazovskaya

    Aggregate Measure for N Data Points

    Olga Yazovskaya

      Hi all,

      I need help with a calculation which looks to be simple...

      I have records where measure is captured every five minutes, I need to give user an option to aggregate data by time of the day.

      And it is easy to do it by Hour with default feature.

      How to give user an ability to pick time interval for aggregation. Like a parameter: 1 hour, 30 minutes, 15 minutes, 5 minutes?


      Thank you in advance

        • 1. Re: Aggregate Measure for N Data Points
          Carl Slifer

          Howdy Olga,


          Create a parameter that has these options that the user can cycle through.

          Then create a calculated field based on this parameter.  In order to make this calculated field there is a bit of thought to be put into it.


          I made a parameter that gives the user the option to switch through the time periods I used values


          I then made a calculated field that took this value from the parameter and turned it into a value to be used in another calculation.



          Finally I referenced this in a calc field

          MAKEDATETIME(DATE(Time),MAKETIME(DATEPART('hour',[Time]),INT(DATEPART('minute',[Time]) / [Interval Time Giver])*[Interval Time Giver],0))


          This is a bit....

          So the rough idea is that it takes the number of minutes and divides this by the value given above - which was chosen by the user.  It will then remove any decimal place and multiply that by the same value. In a nutshell, if the user selected every 5 minutes then 12 will become 10, 36 will become 35, 59 will become 55, and so forth.


          Next we make a time using the hour from the data source and the new minute from the user selection and 0 for seconds.


          Finally we make a datetime which will use the date from the data source and the new time that we've created so far.


          I've attached a workbook as well.



          Carl Slifer


          • 2. Re: Aggregate Measure for N Data Points
            Olga Yazovskaya

            Hi Carl,

            Thanks a lot for your prompt and super detailed response.

            I have tried following the steps you described, but realized that in my case the requirement is a little bit different and I described incorrectly.

            So I have a data which is collected every 5 minutes. So I will have records like: 03:20:00, 03:25:00, however at some point record might be skipped. lets say next record is at 03:35:00.

            So I would need to give an option: show average per time of the day: avg for 5 minutes interval, evg 15 minutes interval, 30 minutes interval.

            Any suggestion on how to accomplish it?