2 Replies Latest reply on Aug 9, 2017 9:15 AM by Motorola Licenses

    hourly calculation

    Motorola Licenses

      Need advise:

       

      Input table:

       

                          Date-HH:MM     Downtime in min

        

      7/1/2017 1:050
      7/1/2017 1:061
      7/1/2017 1:100
      7/1/2017 2:1060
      7/1/2017 3:150
      7/3/2017 3:152880

       

      0 indicates the website is down and subsequent non-zero value tells how long the website was down in minutes.

      how can i get the following output for each occurrence hour:

       

      expected output for specific date and hours only when the website went down and came back up

       

                Date - HH          Downtime

           7/1/2017- 01          1min (for 1:06-1:05)+50min (for 2:00-1:10)= 51

           7/1/2017 - 02          10min (2:10-2:00)

           7/1/2017 - 03         45min (4:00-3:15)

           7/3/2017 - 03           15min (3:15-3:00)

        • 1. Re: hourly calculation
          Chris Geatch

          I think you'll have a problem here, in that you need to know the specific time in order to know how much of the outage to allocate to both this hour and the previous hour.  If you aggregate at the hour level straight away, you have no way of calculating how many of the 60 minutes that occurred from 02:10 were actually between 02:00 and 03:00.  So, I've got part of the way there, in that I can get the correct totals, but only when you leave in the original date-time column.  I can't subsequently remove that column and add the constituent amounts, because I need reference to the next row (using a LOOKUP) to do the calculation.  Maybe you, or someone else, can use that as a stepping stone.  Otherwise, I'd try to transform your data before bringing it into Tableau.

           

          • 2. Re: hourly calculation
            Motorola Licenses

            thanks for the starting point...but i am still wondering how to have a running total for each hour for downtime.