27 January 2017
While manning our support desk you sometimes get some really good Tableau related challenges from your clients. One of the tickets that came in recently was related to calculating the time difference between when people login and people log out of a system. However, and here comes the challenges, we only have one date or timestamp column - logging in and out is defined by a dimension.My initial thought was to create two IF-calculations along the lines of when the dimension is 'login' give me a timestamp and another calculation that does the same for 'logout'. However, and this is quite logic, this will not work. You will have null values in each column of these calculations. Hence Tableau's datediff function will not work (it will basically result in nulls).I went back to the client and suggested to reshuffle the data and create two date columns. One with the timestamp for login and one for when they logout - which would then make the datediff calculation fairly easy. But this wasn’t a feasible solution for the client and so I went back to finding a solution for one date column. Fortunately, the Tableau Community forums are a great resource for problems like this. I found an old thread in which someone had a similar problem and the solution was using table calculations to lookup the two date values you need to pass in the datediff calculation.With the lookup function we can find values in different rows. For example, lookup([Sales],-1) will give you the sales value of the row above the current row (row 0). If we apply this logic to our timestamp column we can find the data in row+1 (the logout row) and detract that from the login row (row 0).I created a dummy dataset for this blog. It has one column called timestamp and a flag with the action (either login or logout). I first started out with this calculation:
However, this doesn’t calculate the time difference correctly. We’ll need Tableau’s datediff calculation to actually calculate on the correct level (minutes).
What I’m doing here is lookup the current row (0) and then do a datediff with the next row (row +1). The unit of date is minutes in this calculation, however, you can also create a parameter that switches between minutes and hours. Just replace the ‘minute’ with your string parameter.
In order to remove the null values you can either replace them with zeros with a zn() calculation or return the value from the next row.
Many people consider Tableau's table calculations a mystery, but I hope I demystified the lookup function in this blog. Please reach out on Twitter or in the comment section below in case you have a problem or you need more information.
However, this doesn’t calculate the time difference correctly. We’ll need Tableau’s datediff calculation to actually calculate on the correct level (minutes).What I’m doing here is lookup the current row (0) and then do a datediff with the next row (row +1). The unit of date is minutes in this calculation, however, you can also create a parameter that switches between minutes and hours. Just replace the ‘minute’ with your string parameter.In order to remove the null values you can either replace them with zeros with a zn() calculation or return the value from the next row.Many people consider Tableau's table calculations a mystery, but I hope I demystified the lookup function in this blog. Please reach out on Twitter or in the comment section below in case you have a problem or you need more information.