6 Replies Latest reply on Jul 26, 2017 10:10 PM by Deepak Rai

    Finding Closest Date to Current Record

    Andy McMaster



      I work in a NPO that works in tissue recovery and we are attempting to create a system to analyze our current processes a little better.  What we are working with is our records are ordered when we recieve possible donor information, we then have many time stamps along the way, but the one we are concerned with is a Tech Dispatch Time (PST) which is formatted as MM/DD/YYYY HH:MM:SS.  What We want to do is do a search if there is a record also dispatched within 180 minutes either before or after as that is the time a recovery takes and would be counted as a Double Dispatch which we using to examine our staffing models.


      Is there a calculation in Tableau that would search both forward and backwards x amount of minutes and if there is a dispatch on any record within a determined time, use a binary marker of 1 or 0 to mark there is so we can start analyzing our data that way?  I know this would be possible through DAX in powerpivot, but I do not have access to my SQL database other then to read premade tables, and my goal would be to allow this to be done exclusively in Tableau to reduce maintenance time as I roll it out.


      Thank you!


      Andy M.

        • 1. Re: Finding Closest Date to Current Record
          Deepak Rai

          Would you mind sharing a workbook based upon mock dataset?

          • 2. Re: Finding Closest Date to Current Record
            Andy McMaster

            Sorry Deepak - new to the Tableau Forum usually google had any answers I needed.  Attached is a very simple attachment narrowed down to four columns of data.  Case ID, If they are full time (1 or 0), Name, Tech Dispatch Time (PST).  All these are set up to have varying times, but at the most basic level represents the only data points I am trying to specifically work with.


            Thank you

            • 3. Re: Finding Closest Date to Current Record
              Deepak Rai

              Hi Andy,

              Looks like I got it. Check the screenshots. I calculated the difference in seconds between successive records and filtered out anything less than 3 hours. Check Screenshots and attached.





              I think doing reverse calculation and filtering anything > 3 hours , you can look other way also, if needed.This would surely give you an idea to implement with actual data.

              • 4. Re: Finding Closest Date to Current Record
                Andy McMaster


                Thank you this is very close to what I am aiming to do and when replicated in my sample set it was able to work.  One item I left out that I believe is the cause of error but didn't think it would matter is there are multiple cases that we record that we don't recover on leaving blanks.  I believe it is causing some errors that I have tried accounting for by also using using the lookup with a +1.  I can show you limited peices of my data for examples



                the far left is case id, right is tech dispatch.  Please keep in mind this is being heavily filtered to remove certain regions, outcomes (such as not recovered, ie) to get to data that only pertains to recovered tissue.  If you look at the rankings by the calculation it is set up



                which states at best there is one 2 hours ahead or 4 hours ahead.  But in realty you can see the closest record is 1 hour 11 minutes.


                Would it be possible to use a rank or index function to rank them correctly then see if the rank above and below are within the limit? Appreciate your patience in helping me, and this technique was really awesome and I learned some cool techniques from it.  I appreciate you sharing your knowledge.


                Andy M.

                • 5. Re: Finding Closest Date to Current Record
                  Andy McMaster

                  I stand corrected.


                  I was able to make some changes that I'm not positive if it had an impact.  I ordered them in my table calculation by rank on the date dispatch and added two calcs one that you had with index - 1 and one with index +1, but difference was I added where it had to be less then and more then the negative/positive value respectively.


                  Verified it over just 70 data points, but it seems to be correct.


                  You are a life saver Deepak.  I will continue to verify my data but at this point it might need some tweaking but your answer was what led me there.


                  Thank you so much again for your time and being part of this wonderful community.


                  Andy M.

                  • 6. Re: Finding Closest Date to Current Record
                    Deepak Rai

                    Glad it Helped & You are Welcome!!!