-
1. Re: How do I use 'start date' and 'end date'
Mavis LiuOct 24, 2018 2:21 PM (in response to Brendan Mackie)
1 of 1 people found this helpfulHi 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
-
2. Re: How do I use 'start date' and 'end date'
Peter FakanOct 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
-
-
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
-
-
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
-
-
5. Re: How do I use 'start date' and 'end date'
Mavis LiuOct 30, 2018 11:39 AM (in response to Brendan Mackie)
-
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!