6 Replies Latest reply on Oct 31, 2018 11:25 AM by Brendan Mackie

How do I use 'start date' and 'end date'

Brendan Mackie Oct 24, 2018 1:57 PM

I know that this question gets asked a lot--and despite trawling through the collected answers, I still can't make anything work for my data. I have worked through the tutorials, read through the step-by-step help and still--I feel like I'm missing something.

My problem:

I have a database of 18th century British clubs. I want to make a line graph showing how many of these are active at any particular time, and then filter these based on criteria like location and club type. But the start date and end date of the clubs are recorded in two fields called 'start date' and 'end date.' I cannot for the life of me figure out how to tell tableau to make a line graph where is shows me, say, on the x axis all the years from 1700 to 1800, and then on the y axis the number of clubs that were active in that time period (i.e. which have a start date equal to or after the current date, and equal to or before the current date.) Each club, fyi, has a unique numerical 'Club ID.'

The background:

This will be a key component of my dissertation research. Because I'm dealing with historical data, and a big sprawling dissertation, I'm afraid that right now my data are a bit messy.

The bribe:

If you help me I'll put you in the acknowledgements to my dissertation and--if it turns into a book--I'll put you in the acknowledgements of my book!

I've attached a packaged workbook.

Clubs Visualization.twbx 196.2 KB

1. Re: How do I use 'start date' and 'end date'

Mavis Liu Oct 24, 2018 2:21 PM (in response to Brendan Mackie)

Hi Brendan,

Unfortunately my internet isn't quick enough to download and work on your workbook! But please see the below instructions:

1) Create a calendar dataset - this is just a list of dates, make sure it includes the min and max of your date range:

2) Inner join this to your dataset using a join calculation of 1, this creates a many to many match and adds in new rows of data. E.g. if the start and end date if 1st jan and 3rd jan, there will be 3 rows of data created:

Then go to a worksheet and create the calculation

Calendar date >= start date
and
Calendar date <= end date

Put this in the filters shelf and set it to true.

Then use calendar dates in your visualisation, and use something like a count distinct of an id to plot the line.

Thanks,

Mavis
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: How do I use 'start date' and 'end date'

Peter Fakan Oct 24, 2018 2:40 PM (in response to Brendan Mackie)
Hi Brendan,

Like this ? note I've filtered the nulls.

Step 1 - drag Start date onto columns shelf

Step 2 - calculate date difference
Step 3 - drag date difference onto size

HTH 18.2 attached

Peter

Clubs Visualization.twbx 203.5 KB
Like Show 0 Likes(0)

Actions
3. Re: How do I use 'start date' and 'end date'

Brendan Mackie Oct 30, 2018 11:34 AM (in response to Mavis Liu)
Thanks very much for this--I made an attempt, but am still... stuck.

I think the problem might be that I am doing the join improperly. When I attempt to join the Calendar Date dataset onto my data, it doesn't know just what to join it onto--the beginning date, the end date, or the IDs of the various organizations in the datasets.... I've tried a bunch and they don't seem to work properly.

I've attempted to re-upload a smaller packaged workbook. But it's still pretty big.

Clubs Visualization .2.twbx 1.8 MB
Like Show 0 Likes(0)

Actions
4. Re: How do I use 'start date' and 'end date'

Brendan Mackie Oct 30, 2018 11:38 AM (in response to Peter Fakan)
Thanks so much for your help, but I'm looking for a different visualization--I'd like the X axis to be year, and the Y access to be the number of clubs which are active during that year.

I made a mistake in joining the tables: here's an updated version if you'd like to take a stab.

Clubs Visualization .2.twbx 1.8 MB
Like Show 0 Likes(0)

Actions
5. Re: How do I use 'start date' and 'end date'

Mavis Liu Oct 30, 2018 11:39 AM (in response to Brendan Mackie)

Hi Brendan,

You need to join it using a '1' key. You'll need to create a join calculation and then just type in 1:

Thanks,

Mavis
Like Show 0 Likes(0)

Actions
6. Re: How do I use 'start date' and 'end date'

Brendan Mackie Oct 31, 2018 11:25 AM (in response to Mavis Liu)

Well it's a Halloween miracle! I fiddled around with the joins and I got the visualization working! Thank you so much for your spooky help!

Now to make sure everything's working out as it should!
Like Show 0 Likes(0)

Actions