2 Replies Latest reply on Apr 20, 2018 1:33 PM by neil.larkin

    Averaging time & date fields for transaction events (Help !)

    neil.larkin

      Hello

       

      I am a new user to Tableau and am trying to calculate average time for given events occurring as transactions. I am able to determine the minimum and maximum time time difference for these events but can't figure out how to get average as i want to be able aggregate 'time to open' rates for different attributes (i.e. email ID, sender ID , recipient ID, daily, weekly etc).

       

      - Max time to open --> DATEDIFF('minute', MIN([Processed time]), MAX([open time]))

      - Min time to open --> DATEDIFF('minute', MIN([Processed time]), MIN([open time]))

       

      Attached is a sample of the data set i am using, each email ID has multiple recipients and multiple events..

       

      A processed event signals the sending of an email, Hence my attempt to calculate other events (i.e. open) from this timestamp in order to aggregate time differences, ideally averages as mentioned..

       

      Appreciate any assistance

        • 1. Re: Averaging time & date fields for transaction events (Help !)
          Zhouyi Zhang

          Hi, Neil

           

          from below sample data, what's your expectation of average time and how it is calculated?

           

          ZZ

          • 2. Re: Averaging time & date fields for transaction events (Help !)
            neil.larkin

            Hi ZZ

             

             

             

            Sender sends 1 'processed' timestamp to each Receiver id. Therefore this Processed timestamp is unique to what email ID and Receiver ID it has been sent.

            Once processed, any number of transactions can occur pertaining to a Receiver ID. Often for open and click transactions these can occur more than once for each email id. Resulting in a Receiver having multiple open timestamps for each email ID, hence the challenge in averaging the time between single processed timestamp to multiple open timestamps.

             

            i have dropped in a manipulated view of email id 203185 below which is representative of broader population.

             

            you can see that some Receivers like id 195 have multiple open timestamps as they opened email more than once.

             

            I can calculate distinct open counts, open % rates etc. However how do i average the the time difference between the 6 open events to 1 processed event?

             

                 

            email IdTimesender  Idreciever idtransaction history
            20318501/01/2018 13:019339191processed
            20318501/01/2018 13:019339192processed
            20318501/01/2018 13:019339193processed
            20318501/01/2018 13:019339194processed
            20318501/01/2018 13:019339195processed
            20318501/01/2018 13:019339196processed
            20318501/01/2018 13:019339197processed
            20318501/01/2018 13:019339198processed
            20318501/01/2018 13:019339199processed
            20318501/01/2018 13:179339195open
            20318501/01/2018 13:059339197open
            20318501/01/2018 13:099339198open
            20318501/01/2018 13:029339199open
            20318501/01/2018 13:069339195open
            20318501/01/2018 13:039339195open
            20318501/01/2018 13:039339199open
            20318501/01/2018 13:029339191open
            20318501/01/2018 13:029339195open
            20318501/01/2018 13:049339195open
            20318501/01/2018 13:119339195open
            20318501/01/2018 13:139339196open
            20318501/01/2018 13:199339195open
            20318501/01/2018 13:169339191open
            20318501/01/2018 13:019339191delivered
            20318501/01/2018 13:019339192delivered
            20318501/01/2018 13:019339193delivered
            20318501/01/2018 13:019339194delivered
            20318501/01/2018 13:019339195delivered
            20318501/01/2018 13:019339196delivered
            20318501/01/2018 13:019339197delivered
            20318501/01/2018 13:119339194deferred
            20318501/01/2018 14:049339193deferred
            20318501/01/2018 13:249339191click
            20318501/01/2018 13:269339192click
            20318501/01/2018 13:249339193click
            20318501/01/2018 13:299339194click
            20318501/01/2018 13:239339195click
            20318501/01/2018 13:299339196click
            20318501/01/2018 13:259339197click
            20318501/01/2018 13:319339191click
            20318501/01/2018 13:349339191click