3 Replies Latest reply on May 27, 2016 12:35 AM by renske.hercules.0

    Calculate average starttime

    renske.hercules.0

      Hi All,

       

      I want to calculate the average starttime for these dates. Does anyone know what formula to use?

       

      Thanks!

       

        • 1. Re: Calculate average starttime
          Carl Slifer

          Howdy Renske,

           

          One of the first things you will need to know is that Time doesn't have a meaning in Tableau, rather date time does. This is of course just he combination of date and time. If you put that table in tableau depending on your source data formatting Start will be a string field (abc) or a datefield but the date will be 12/31/1899.

           

          The second thing we should know is that 1 day = 1 unit or 1 in terms of integers. So our time is not time how we think about it but rather a portion or a percentage of a day.  We want to take our time and convert it into seconds and then we will divide that by how many seconds in a day so that we get the average percentage of a day. Then we will convert this value into a format based on time.

           

          AVG((DATEPART('hour',[Start])*3600

          +

          DATEPART('minute',[Start])*60))

          /

          86400

           

          What the above will do is pull the hour out and multiply that by how many seconds there are in an hour (3600), it then does the same for the minute part of your date and multiplies that by 60. Then we add those values up.

           

          Next we will average then using the AVG function so that we have the average number of seconds for start time. Then we divide this by 86400 (number of seconds in a day). This gives us the average percentage of a day.

           

          Finally Right Click the measure in your measures Pane, go to default properties, number format, custom and type in hh:mm. (See the attached workbook)

           

          You may want to make use of the function MAKEDATETIME() as well which will allow you to combine your data and time fields that you're using in one time stamp for further analysis or future reference.

           

          Cheers!

          Carl Slifer

          InterWorks

          • 2. Re: Calculate average starttime
            renske.hercules.0

            Hi Carl,

             

            THanks so much for your reply. I do get the first calculation, but how do I convert it back to a time (dd/mm/yyy - hh:nn) formatting? I need this formatting to connect it to another formula. Now I have the time in a percentage of the day. I tried MADEDATETIME, But its not really working, or I'm doing something wrong..?

             

            Thanks!

            • 3. Re: Calculate average starttime
              renske.hercules.0

              I got it to work now. Is this the only/best way to calculate a breakdown?