3 Replies Latest reply on Nov 17, 2016 2:35 AM by Deborupa Banerjee

# Calculation on Dates

Hi all

I am working with some sales data that each transaction has multiple stages. say stage A, B, C, D...

In this scenario, each transaction need to go to each stage and I want to calculate the difference the transaction stays at each stage.

Say the data might look like this:

transaction_id    stage updated_date
1                 A        2015-01-01
1                 B        2015-01-02
1                 C        2015-01-05
1                 D        2015-01-06

I want something like:

transaction_id    stage time_spent
1                 AB       1 day
1                 BC       3 days
1                 CD       1 day

Then I will be able to analyze the time spent at each stage and look at the histogram or distribution. However, the only thing I can find is the function datediff.

Can anyone show me how to achieve this in Tableau?

Deborupa

• ###### 1. Re: Calculation on Dates

Hi Deborupa,

You could try using Lookup() to compute the duration using the date in the previous row of your partition:

`DATEDIFF('day', LOOKUP(ATTR([Updated Date]), -1), ATTR([Updated Date]))`

Then to get your "AB" labels, use `LOOKUP()` again to fetch it from the previous row:

`LOOKUP(ATTR([stage]), -1) + ATTR([stage]) `

Please let me know if that helps.

Regards,
Kajal

1 of 1 people found this helpful
• ###### 2. Re: Calculation on Dates

here is my view on it.

Create Calculated fields to generate Stage Change and Time Spent

Stage=attr([Stage])+''+LOOKUP(min([Stage]),1) this would give next stage along with current stage

so A,B,C,D is now converted to AB,BC,CD,NULL

Time Spent=DATEDIFF('day',attr([Date]),LOOKUP(min([Date]),1))

this gives difference between current stage date and next stage.

To get similar look as you final result, you have to hide Date column and  Exclude Null row.

and you can create bar chart as below for your analysis

2 of 2 people found this helpful
• ###### 3. Re: Calculation on Dates

Thanks Kajal and Amit.