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

# Calculating product age between activation date and latest report date

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.

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

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

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

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

Hi Elina,

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

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

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