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

    Calculation on Dates

    Deborupa Banerjee

      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?
      Thanks in advance.

       

      Deborupa

        • 1. Re: Calculation on Dates
          Kajal Chandani

          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
            Amit Narkar

            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

             

            Days Spent.png

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

              Thanks Kajal and Amit.
              Your solutions worked as desired.

               

              Deborupa