4 Replies Latest reply on Mar 7, 2017 9:20 AM by Ivan Young

    Can I use an LOD expression to determine time difference between occurrences?

    Lauri Jalonen

      Hi Tableau gurus out there,

       

      I've created an LOD expression called "detections per device" in the attached workbook:

       

      { FIXED [Device Id]: COUNT([Id])}

       

      I then used that LOD to extract the number of detections per device id to create various groups of #detected:

       

      IF [detections per device] > 0 and [detections per device] == 1 THEN

      "Seen only once"

      ELSEIF [detections per device] == 2 THEN "Seen twice"

      ELSEIF [detections per device] == 3 THEN "Seen 3 times"

      ELSEIF [detections per device] > 3 THEN "Seen more than 3 times"

      END

       

      So far it's simple. Next step: I need to figure out the time difference between each detection per device, as I need to get an average time between visits.

      How would I go about this? I'm not sure how to enhance my LOD.

       

      Any help is greatly appreciated!

        • 1. Re: Can I use an LOD expression to determine time difference between occurrences?
          Jim Dehner

          Hi

          Not sure I can follow all the calculations but see how this works for your need

           

          Let me know if it helped

          Jim

           

           

                   >> if [Distinct device count]>1 then avg([Dwell time (min)]) end

          • 2. Re: Can I use an LOD expression to determine time difference between occurrences?
            Lauri Jalonen

            Thanks for a swift reply Jim Dehner ! What you're using is the session dwell time. What I'm looking for instead is the time between each session, so somehow get the timediff between the end of the previous session and the start of the next, but do it separately for each device.. does that make sense?

            1 of 1 people found this helpful
            • 3. Re: Can I use an LOD expression to determine time difference between occurrences?
              Jim Dehner

              Hi

              I didn't forget you but this was tough

              I have to admit I was not able to understand your data sheet but I was able to do a simulation using Superstore Data - see attached 10.1 workbook

               

              The issue I was having was determining the last event finish and next event start - so I simulated that using the sales data Order Date and Ship Date

              I think that is OK you have separate start and finish dates

              I broke the process into steps (you may be able to combine some )

              1. Calculated the Current Order date >> float(LOOKUP(ATTR([Order Date]),0))  (this is the start of the NEXT event)
              2. Calculated the last ship date >>>Float(LOOKUP(ATTR([Ship Date]),-1)) (this is the finish of the LAST event)
                1. Note the Float is need for the next calculation
              3. Calculated the difference >>if  ISNULL([last ship date ]) then 0 Else [current order date]-[last ship date ] END
                1. Note in my example the date fields are at the day level so the difference is in days as whole numbers -
                2. your dates will be at the date/time level so the difference will be in days including decimal parts - you can convert to min or what ever you need
              4. Each of the above 3 is a table calculation that is evaluated at the Order ID level and restarted every customer

              I just made a text table to see the calculations - it looks right but you know your data

               

              Does this make sense and can you draw the analogies to your data?

               

              Let me know if this helped

              Jim

               

              • 4. Re: Can I use an LOD expression to determine time difference between occurrences?
                Ivan Young

                Hi Lauri,

                I didn't download your workbook as it's quite large but I have come across this issue before.  Check out this post and see if it helps with your problem.

                 

                What I did was create 3 LODs:  Min Date by Device, Max Date by Device, # of IDs by Device -1(Not counting the first ID).  I then created a datediff between min and max dates which I then used as the numerator for the average calculation.

                 

                Regards,

                Ivan

                 

                 

                Average of days from last transaction