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

How can I calculate lead and lag values?

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:

 ClientID EventDate Client1 1/01/2000 Client1 30/06/2000 Client1 25/08/2000 Client2 15/03/2000 Client3 13/02/2000 Client3 25/02/2000 Client3 17/07/2000

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

 ClientID EventDate ObsNumber DaysToNext Client1 1/01/2000 1 181 Client1 30/06/2000 2 56 Client1 25/08/2000 3 null Client2 15/03/2000 1 null Client3 13/02/2000 1 12 Client3 25/02/2000 2 143 Client3 17/07/2000 3 null

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?

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?

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.