5 Replies Latest reply on Jul 22, 2016 10:00 PM by Ashish Chaudhari

Help with "partitioning" an interval between two dates calculation

Hello, I've created a calculation to find the number of days between first and last purchase date however I want to "re-set" the count every year. Can anyone help? Example packaged workbook attached. Please save any work in 9.2 or paste the answer. Thanks,

• 1. Re: Help with "partitioning" an interval between two dates calculation

Hi Matt,

I guess you forgot to attach the workbook. I can't see any attachment.

Thanks,

Ashish Chaudhari

• 2. Re: Help with "partitioning" an interval between two dates calculation

Ashish Chaudhari thanks! see attached.

• 3. Re: Help with "partitioning" an interval between two dates calculation

Hi Matt,

Thanks for attaching the file. here is your calculated field that you are looking for. Let me know if this suffice your need.

if YEAR([First Purchase Date])=YEAR([Date]) then DATETRUNC('day', [Date])-DATETRUNC('day', [First Purchase Date])

ELSE DATETRUNC('year', [Date])-DATETRUNC('year', DATEADD('year',DATEDIFF('year',[First Purchase Date],[Date]),[First Purchase Date]))

END

Another thing which I would like to tell you can if you want to keep the day of purchase day intact and then you want to calculate the difference then you can make simple change as below.

if YEAR([First Purchase Date])=YEAR([Date]) then DATETRUNC('day', [Date])-DATETRUNC('day', [First Purchase Date])

ELSE DATETRUNC('day', [Date])-DATETRUNC('year', DATEADD('year',DATEDIFF('year',[First Purchase Date],[Date]),[First Purchase Date]))

END

You will get the below result.

Please find the attached workbook (tableau 9.3). I guess you are using older version. You won't be able to open this. Hence I would request you to follow the above instructions.

Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• 4. Re: Help with "partitioning" an interval between two dates calculation

Ashish Chaudhari thanks. The first one worked for the result I wanted. Thanks!

• 5. Re: Help with "partitioning" an interval between two dates calculation

Hi Matt,