4 Replies Latest reply on Jun 9, 2016 11:40 AM by Aman Chauhan

    Date Function

    Aman Chauhan

      Hi All,

       

      I have data as shown below-

       

      Name     Registration Date               Purchase Date

      John        01-01-2016                           06-03-2016

      John        01-01-2016                           05-24-2016

      John        01-01-2016                           03-01-2016

      John        01-01-2016                           01-06-2016

       

      I need to do following two things-

       

      1. Filter out the record with the first Purchase date which occurred right after the Registration date. In this example the one marked as Yellow is the record I want.
      2. Next find the No. of Days between the two dates. In this Example the No. of Days will be 5 Days (01-01-2016 to 01-06-2016 = 5 Days)

       

      Tableau Version: 9.3.1

      Data Source Type: MS Excel

        • 1. Re: Date Function
          Tina Hauser

          Hi Aman,

          Please see the attached.

           

          I created a calculated field using Level of Detail to get the minimum purchase date.

          Then I created a Boolean to identify if each record's purchase date = the minimum or not, and filtered out where it does.

           

          There is a calculated field under measures to get the difference between registration and the minimum purchase date.

           

          Let me know if you have questions.

          • 2. Re: Date Function
            Aman Chauhan

            Hi Tina,

             

            Thanks for the quick response.

            Sorry for these basic questions I am really new to tableau

             

            I have one quick question-

             

            What if instead of Records for just John I have multiple records like shown below-

             

             

            Name     Registration Date               Purchase Date

            John        01-01-2016                           06-03-2016

            John        01-01-2016                           05-24-2016

            John        01-01-2016                           03-01-2016

            John        01-01-2016                           01-06-2016

            Mike        02-01-2016                           09-04-2016

            Mike        02-01-2016                           06-14-2016

            Mike        02-01-2016                           04-20-2016

            Adam        02-01-2016                           04-10-2016

            Adam        02-01-2016                           09-04-2016

            Adam        02-01-2016                           06-14-2016

            Adam        02-01-2016                           04-20-2016

            Adam        02-01-2016                           04-10-2016

             

            So now i need to find for All i.e. John Mike and Adam

            • 3. Re: Date Function
              Tina Hauser

              The calculation for min(purchase date) is a level of detail calculation that I fixed at the Name level. This means that it will calculate the minimum by name, so adding more names will not be an issue.

               

              I updated the data to match your new example and added it to the workbook - please see Sheet 2.

              • 4. Re: Date Function
                Aman Chauhan

                Ok Thanks For your Help!