6 Replies Latest reply on Jan 4, 2013 11:02 AM by donna trinh

    Calculated field - Date

    buildstar

      Hi All,

       

      I have the following data as attached in excel.

       

      I want to create a calculated field for a date duration that can subtract the UN date with I date(Date difference) for a given ID.

       

      I want to see how long a user had the product installed.

       

      Can any one help with a sample calculated field that will help achieve this?

       

      Thanks

        • 1. Re: Calculated field - Date
          Mark Holtz

          Hello,

          I do not understand your question. You have a [Week_End_Date] field and a [Date] field in your data. The ID field looks like a text string that has a date encoded in the first 8 characters.

           

          You can use DATEDIFF('day',[StartDate],[EndDate]) to calculate the difference in time (in days) between two date-formatted fields. 

          If you want to convert your ID field to a date for use as either the [StartDate] or [EndDate], you could do so with:

          DATE(
          MID (STR([ID]/1000000000),5,2)// month#
          +'/'+
          MID (STR([ID]/1000000000),7,2) // day#
          +'/'+
          LEFT(STR([ID]/1000000000),4) // year#
          )

           

          Does that help?

          • 2. Re: Calculated field - Date
            donna trinh

            try zn(datediff('day',lookup(attr([weekenddate]),attr([date])

            • 3. Re: Calculated field - Date
              Alex Kerin

              Edit: I  assume you are referencing the answer to your question here: http://community.tableau.com/thread/122301

              We may need to use a lookup, but Gallop has not provided enough information yet.

               

              Your calculation as it stands does not work, and we would need to understand the partitioning.

              • 4. Re: Calculated field - Date
                Alex Kerin

                Every ID in that data file only has one product and either I or UN associated with it, but never both.

                 

                I would expect that we are missing data here

                 

                From your explanation, I would expect to see:

                 

                An ID with a product name (maybe more), and for each ID/product combination, an I (installed) row and a UN (uninstalled) row

                 

                I'm guessing the week_end_date is irrelevant, as is the fact the ID looks like a date string? Thanks for mocking up the data - I think we just need a better set of mocked up data.

                • 5. Re: Calculated field - Date
                  Alex Kerin

                  Here's an example with a better data set. I had to include a dummy row because JET was changing the ID to a number with an exponential. I forced it to a text column.

                   

                  It uses: datediff('day',lookup(attr([date]),-1),attr([date]))

                   

                  And the partitioning uses date to order the lookup, with product and ID on the left side of the advanced calculation block (as we want each unique install)

                  • 6. Re: Calculated field - Date
                    donna trinh

                    Your sample data does not contain double entries (one for install day and the other for uninstalled date). For each ID, you should have the start and end date per ID in each row.