6 Replies Latest reply on Nov 14, 2016 12:10 AM by Elina Grin

    Calculating product age between activation date and latest report date

    Elina Grin

      Hi!

      I need to calculate product age in years and also in months using 2 columns - First Activity(YYYY.MM.DD) and Report date (YYYY.MM.DD) using latest report date available. Latest report date should be dynamic since monthly data will be added regularly.

       

      Can anyone please help?

        • 1. Re: Calculating product age between activation date and latest report date
          Ravindra Zinjad

          Hi Elina,

           

           

          If Tableau knows they are datetimes you can use the DATEDIFF() function to find the difference in units of 'day', 'hour', 'minute' or 'second', or you can just subtract the start from the finish and that will give you a floating point number representing the (fractional) number of days between the two (i.e. if the answer is 1.25 that means 1 day and 6 hours).

           

          If your fields are strings you'll need to convert them to datetimes first.  You'll need to reformat slightly to get those strings into a format the DATETIME() function recognises (easiest is just to insert a couple of "/" characters.

           

           

          Regards,

          Rav

          • 2. Re: Calculating product age between activation date and latest report date
            Norbert Maijoor

            Elina,

             

            Find my approach as reference below and stored in attached workbook 9.3   

             

             

             

            Aging in Days: DATEDIFF('day',[Product Activation Date],[Today])

            Aging in Months: DATEDIFF('month',[Product Activation Date],[Today])

             

            [Today]  could be made dynamically based on latest date generated by source

            • 3. Re: Calculating product age between activation date and latest report date
              chan sing

              Can you not use the TODAY() function get the report date and use it to calculate the Age?

               

              DATEDIFF('year', FirstActivityDate, TODAY())

              DATEDIFF('month', FirstActivityDate, TODAY())

               

              Use these as 2 calculated columns.

              • 4. Re: Calculating product age between activation date and latest report date
                Ravindra Zinjad

                Hi Elina,

                 

                Adding to my explation please find below formula to calculate difference.

                 

                Note : Replace Now with current date, and order date with First Activity.

                Creat Year Diffference formula.

                IF DATEPART('year',NOW())-DATEPART('year',[Order Date]) <0

                THEN (DATEPART('year',NOW())-DATEPART('year',[Order Date]))*-1

                ELSE DATEPART('year',NOW())-DATEPART('year',[Order Date]) END

                 

                Create Month Diff Formula

                 

                 

                IF DATEPART('month',NOW())-DATEPART('month',[Order Date]) <0

                THEN (DATEPART('month',NOW())-DATEPART('month',[Order Date]))*-1

                ELSE DATEPART('month',NOW())-DATEPART('month',[Order Date]) END

                 

                Concatenate this 2 formula:

                 

                STR([Year Diff])+' Years ' + str([Month DIff])

                 

                 

                 

                Output as below :

                 

                Regards,

                RAV

                1 of 1 people found this helpful
                • 5. Re: Calculating product age between activation date and latest report date
                  isa.serah

                  Hi Elina ,

                   

                  I had same question before and this community helped me with this formula...

                   

                  LEFT(STR(INT(MAX([Months for age cal])/12)),2) + 'years ' + '  '+ RIGHT(STR(MAX([Months for age cal]%12)),4) + 'Months'

                   

                  where the calulated Field '[MOnths of age cal] have the formula,,,

                   

                  DATEDIFF('month',[FirstActivityDate],TODAY())

                   

                  then you will get result as say for example 3 years and 11 months  in that pattern.

                  • 6. Re: Calculating product age between activation date and latest report date
                    Elina Grin

                    Thank you everyone for your responses. I wanted to say that the difficulty of calculating age was due to the fact that I didn't know how to find the latest report date. Comments above assumed that the latest report date is today, however in my case it was months before.

                    In case anyone will find this useful - here's how I solved it.

                    1. Created calculated field to get to the latest date available

                    { FIXED : MAX([Report Date])}

                    2. Then calculated age using Datediff function & newly calculated field