2 Replies Latest reply on Jul 18, 2016 7:02 AM by Tommy Sims

# Seeking assistance on converting Dates (exact) to Day 1, Day 2, ...

Hello all,

My issue is related to converting dates. I have a data set of Job Postings that have a 30 day life cycle. I would like to look at which days of the life cycle perform the best based on applications. The issue I have is that all of the job postings do not start on the exact same date. My data includes the start date, the end date, and each date that received an application (so there are dates with no data). I would like to make the exact date irrelevant and show only Day 1 of job life all the way through Day 30. Could anyone assist with an equation that could be used here? I am stumped at this point and would appreciate any assistance. Thanks.

In this screenshot we see the unique job ID, the start date, end date, and each date the job received an application. I would like to make the start date day 1 for my analysis and then the next day to be day 2 ect..

Tommy

• ###### 1. Re: Seeking assistance on converting Dates (exact) to Day 1, Day 2, ...

Hi Tommy!

If you are using Tableau Desktop 9.0 or later, you can use level of detail calculations to find the start date per job ID, using a formula like the one below:

{FIXED [Job ID]: MIN(Date)}

We can then get the difference per line, using a date difference calculation, which finds the difference between the start date, and the line item date, like this:

DATEDIFF('day',[!Start Date], [Date])

We can also add one day, so that the start day is "Day 1" rather than "Day 0."

I have attached a workbook demonstrating this solution.