7 Replies Latest reply on Nov 7, 2018 8:26 AM by Seth Nelson

# Calculate Number of Days Elapsed Since Start of Events

I am trying to create a calculated field that marks the number of days elapsed since the start of various events.  The goal is to plot events that take place at different times each year by the day of the event (weekends included).  For example, we have seasonal training windows, such as winter training, spring training, etc.  We offer lunch and learns where attendence is recorded. The training windows are not in the exact date window each year.  It is the goal of this project to show how many people attend the lunch and learns on day 1 of each training window each year, day 2, day 3, etc.   I have a table below illustrating what I have and what I want to get from it.  I havent tried anything yet, but I know I want to use the 'Training Window' field and the 'Date' field to calculate the 'Days Since Start of Window' field.Any help would be much appreciated.

Current Table

Training WindowDateNo. of Attendees
Winter12/1/201712
Winter12/4/201721
Winter12/5/201714
Winter12/6/201716
Winter12/7/201732
Winter12/8/201714
Winter11/28/201613
Winter11/29/201625
Winter11/30/201625
Winter12/1/201620
Winter12/2/201613
Winter12/5/201614
Spring4/1/201710
Spring4/2/201714
Spring4/3/201732
Spring4/4/201731
Spring5/1/201618
Spring5/2/201617
Spring5/3/201630
Spring5/4/201640

Table with additional field I would like calculated:

Training WindowDateNo. of Attendees
Days Since Start of Window
Winter12/1/2017121
Winter12/4/2017214
Winter12/5/2017145
Winter12/6/2017166
Winter12/7/2017327
Winter12/8/2017148
Winter11/28/2016131
Winter11/29/2016252
Winter11/30/2016253
Winter12/1/2016204
Winter12/2/2016135
Winter12/5/2016148
Spring4/10/2017101
Spring4/11/2017142
Spring4/12/2017323
Spring4/13/2017314
Spring5/9/2016181
Spring5/10/2016172
Spring5/11/2016303
Spring5/12/2016404
• ###### 1. Re: Calculate Number of Days Elapsed Since Start of Events

Hi John,

Tough to provide a solution without a workbook. Would it be possible for you to upload a sample workbook with the data in your current format?

I'm thinking you will need a calculation to isolate the minimum dates for each season/year, and then a DateDiff calculation to give you the day count you are looking for.

Hope that helps,

Seth

• ###### 2. Re: Calculate Number of Days Elapsed Since Start of Events

Greetings John!

You might find useful this thread which resembles what you are trying to do:

• ###### 3. Re: Calculate Number of Days Elapsed Since Start of Events

Hi Seth,

I am thinking along the same lines as you.  Unfortunately, I cannot share a workbook or the data.  A lot of our company policies prevent open sharing.  I was hoping the sample data I created above would help.  If I cannot get close to an answer by the end of the work day, I will create a fake dataset and share the workbook from my home computer.

The DATEDIFF part is what I have found in my research so far, but I struggle with isolating the training windows by date.

I tried the solution presented HERE: https://talkdatatome.net/2014/05/27/plotting-two-different-time-series-on-the-same-line-graph-in-tableau/

But that did not get me to where I needed.  Thank you for responding!  I will try to do a better job of sharing so my questions can be answered with more efficiency.

• ###### 4. Re: Calculate Number of Days Elapsed Since Start of Events

I think I can modify this resource a little bit to test it.  I will let you know the outcome.

Thanks!

• ###### 5. Re: Calculate Number of Days Elapsed Since Start of Events

Hi John,

To isolate dates and then compare, you could try something similar to this approach:

MIN Training Window:

{ FIXED [Training Window],YEAR([Date]): MIN([Date])}

Days Since:

DATEDIFF('day',[MIN Training Window],[Date])

Let me know if that helps.

Seth

1 of 1 people found this helpful
• ###### 6. Re: Calculate Number of Days Elapsed Since Start of Events

Seth,

This did exactly what i needed.  Who knew it was so simple?  Apparently you did.

Thank you very much

JT