2 Replies Latest reply on Sep 10, 2018 5:18 PM by Christine___

    How can I calculate lead and lag values?

    Christine___

      I'm trialling Tableau Prep as a potential replacement for SAS/SPSS to do data cleaning (and don't use Tableau so don't know the conventions). A couple of common activities that I do that I can't seem to figure out how to do in Tableau Prep is to make a running tally of each observation per client and calculate the days to the next observation. Solutions in other software include:

      • Left join the next date for each client and compute the days to event
      • Reshape the data long to wide within client and calculate a simple date difference between date 1 and date 2
      • Calculate a lag/lead date and compute a date difference

      I can't seem to figure out how to do any of these in Tableau Prep. Any advice would is welcome! I don't need an ongoing solution because I just work with one-off data extracts and surveys.

       

      My data are stored like this:

       

      ClientIDEventDate
      Client11/01/2000
      Client130/06/2000
      Client125/08/2000
      Client215/03/2000
      Client313/02/2000
      Client325/02/2000
      Client317/07/2000

       

      I need this at a minimum (obsnumber is useful so want to specifically compute this):

        

      ClientIDEventDateObsNumberDaysToNext
      Client11/01/20001181
      Client130/06/2000256
      Client125/08/20003null
      Client215/03/20001null
      Client313/02/2000112
      Client325/02/20002143
      Client317/07/20003null

       

      It would be helpful to know if Tableau Prep can reshape long to wide like this (I wouldn't necessarily do this for dates, but would need it for other variables):

       

      ClientIDEventDate1EventDate2EventDate3
      Client11/01/200030/06/200025/08/2000
      Client215/03/2000
      Client313/02/200025/02/200017/07/2000
        • 1. Re: How can I calculate lead and lag values?
          Joshua Milligan

          Christine,

           

          This is one place where Tableau Prep doesn't have an easy solution yet.  There are, however, possibilities for tackling this kind of problem:

          • In some cases it is possible to unpivot the data (turn rows into columns).  Based on the data you shared, I don't see this working well in your case.
          • Depending on your data source:
            • Excel, flat-file, and the like: Since it's a one-time thing and you don't need an automated repeatable process, you might consider manually adding a row number / index to your source data.  Then you could do a left join on Row = Row + 1.  With a bit of calculation logic, you could see if the client matched between the joined rows and then get the date difference.
            • SQL Server, Oracle, and anything that supports table calculations: use the table calculations in a custom SQL
          • Use a ranking work-around to get the "row number" in Tableau Prep and then solve with a left join like above

           

          Ultimately, Tableau Prep needs functionality that handles your use case and similar use cases.  I'm very hopeful it will in the near future.  Please consider voting for this to lend your support where the developers can see how much it is needed.

           

          Hope that helps!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: How can I calculate lead and lag values?
            Christine___

            Thanks, the source is excel. It's very easy to do most of this directly in excel, however in my case although I don't need automation I do need reproducability which means that every data step should be documented and auditable from the point of opening the data source onwards. I shall vote.