You need to transpose your date from columns to rows so that you can get all the date values in one field. There are several ways to transpose your data in tableau. I don't know which data source you're using. If you're using excel source then you can use Pivot option to transpose your data. See the screen shot. Select your date fields and choose pivot option from the down arrow.
You'll get this view
Now go to worksheet and create your viz. Use count/countd() function to get the count of guests
Hope this will help. Sample workbook v10.5 attached for your reference,
Book2_v10.5.twbx 11.2 KB
This helps a lot!!
One issue: Your method puts a mark on the date where the guest's stay begins, and one where it ends, but nothing in between.
For example, Guest 1, from Spain, shows a red mark at 2003, and one at 2015. But there are no red marks in between.
Can Tableau recognize Start Dates & End Dates and interpolate the data in between?
Please see my recommendation, which uses this Tableau reference ( How to display a daily number of guests based on the start and end dates of their stays at my hotel ?) supplied by kettan for us of a Cartesian join (1:1) between Reservation Dates and Contiguous Dates (fact table). A filter then checks to see whether the reservation dates fall within the contiguous dates (T|F). Filtered to True. Number of records found are then displayed by date.
Both MS-Excel worksheets and the workbook (2018.1 version) are attached as reference. Screenshot below. If this resolves your question, please mark this response as correct so that others may find it useful. Thanks! Don
Wow, what an incredibly relevant discussion to link to!! I swear I just used the motel situation as an example — my project has a completely different focus (about politicians in office) — so that coincidence is extra wild for me!
Anyway, I tried out the method, and it mostly worked great!
The main issue is that there are several dates with NO entries, and the graph seems to skip them over — i.e. let's say there was nobody staying from June 5-8... the dates along the x-axis would read: June 1, 2, 3, 4, 9, 10, 11 etc...
In other words, the gap from June 5-8 isn't visually represented. What's the best way around that?
And I got the color-by-location to work, but I have a new challenge:
Would it be possible to simultaneously show a line denoting the total number of rooms AVAILABLE? the hotel example doesn't really make sense any more, but basically as if the hotel had 10 rooms at the beginning of June, then expanded to 15 rooms on June 8, then 20 rooms on June 15, and finally 30 rooms on June 28.
Could we somehow visually track the number of rooms available?
Thanks so much for your continued help!!
Yes, both requests are feasible. Please see the newly attached workbook. I modified the original MS-Excel data to exclude the dates you requested. As seen below, the dates remain contiguous however because there is no data, there will not be a graphical representation other than the contiguous dates.
The below also shows the calculation which hardcodes the number of rooms available by Date span as requested, however, only if there are guests. If there are no guests, then the date is still returned on the axis to show continuity to the next available date where there are guests and rooms.
Another Way shows how it could be done using the new Step Line feature in Tableau, which might more closely represent what you're trying to achieve?
This solution involves using the dual-axis feature to overlay one chart onto another chart with the same type of x-axis, then synchronizing the y-values axis. If this resolves your question, please mark this response as correct so that others may find it useful in the future. Thx, Don
Dates to Timeline.twbx 41.5 KB
Awesome!! Getting so close --
Thank you for making workbooks for this little problem! Two questions:
1) I still don't understand how you got Tableau to add spaces for those null dates. When I try to recreate this with my dataset (which involves years, not days), I get an x-axis like this:
As you can see, it skips 1872-1875, and 1882-1967. I would like it if there were just empty spaces there, like in your workbook. How did you accomplish that?
2) Is there a way to do the secondary graph ("total number of rooms available") without hardcoding it? By integrating another excel spreadsheet, perhaps? My secondary dataset has a couple dozen changes through the timeline, and I'd rather not retype it all as code...
Thank you again!
The reason the date axis is continuous is due to the Cartesian (1:1) join that I did between two different MS-Excel worksheets. Worksheet #1 simulated the reservations (Guest Name, Date Arrived, Date Left). Because the date data is intermittent, meaning there are gaps in it like your YEARS data, then we need to attach another range of contiguous dates which is in Worksheet #2.
So, if you were to join the worksheet you have in your example with a new Excel worksheet which is a simple column of contiguous years using the cartesian join method shown in the screenshot below, then you should be able to achieve the effect that was in the last example workbook I provided.
The calculation for number of rooms available across dates is:
IF ATTR([Dates])>=DATE('2018-06-01') AND ATTR([Dates])<=DATE('2018-06-07') THEN 10
ELSEIF ATTR([Dates])>=DATE('2018-06-08') AND ATTR([Dates])<=DATE('2018-06-14') THEN 15
ELSEIF ATTR([Dates])>=DATE('2018-06-15') AND ATTR([Dates])<=DATE('2018-06-27') THEN 20
ELSEIF ATTR([Dates])>=DATE('2018-06-28') AND ATTR([Dates])<=DATE('2018-06-30') THEN 30
It's just a matter of adding and deleting what's above by copy and pasting. There might be another way of doing what you're trying to achieve, but so far I don't have any relevant data to work with and I would be guessing and I'd probably fail. So unless you'd like to supply your workbook, there's not much more I can do aside from the examples already provided. Thx, Don
Ah, sorry my example made things more complicated instead of simpler — I should've just shared my project from the beginning.
Attached is my workbork. The project is looking at the number of African American members of US Congress over time. One chart for the Senate, one for the House. I'm working on the Senate first, as a test run, since that list is a lot shorter (10 senators, vs 140 reps)
Thank you taking the time to explain the 1:1 join, but I do understand that part (thanks to your previous posts). I had successfully done the join. I'm not sure the difference between continuous dates and contiguous dates in this instance, but my Lookup sheet contained every single year between 1870 and 2018. In my Workbook, you'll see the Data Source page shows entries for the years 1882-1967 (when there were no black Senators)... but still, in the visualization, those years are not shown.
As for the Secondary Line, typing out the hard code for the Senate wasn't too bad. I'd still like to know if there's an easier way — since the House has changed size about 20 times (since 1870), and the more data is hand-copied, the more it's prone to error.
But if hard-coding is the best way to go, I don't mind putting in the time!
I'm sorry for adding extra confusion to all of this, and you will definitely get the "Correct Answer" credit on this one!
BlackSenators.twbx 34.3 KB
Only a couple of minor things needed to be synced up. I changed the filter calc to match on year of the date-part value for year and changed Year(Years) in the columns shelf from Discrete to Continuous and that brought in your contiguous years. I also changed the axis to show more years as much as possible, it may not look like it due to the time span, but they're all there. Ideally, the number of Senate Seats would be in a separate table to make things easier instead of doing a calc. as simply a separate column of data by year and number of seats available. I reattached your workbook...
Hope that helps! Thx, Don
BlackSenators.twbx 21.5 KB
Incredible! This is so very very close!
I only see one issue: The Secondary Graph (Total Senate Seats) looks wrong during the "gap years" (e.g. 1881-1967).
You see that big slope in the middle? That's not what the hard-coded data would make it look like... it shouldn't even have any slope, it should always be steps upwards...
You suggested I make a separate table with the available Senate Seats on it... could I do that in the "Lookup" spreadsheet? Or would that mess up the Cartesian join? What's the best way to do that?
So in your workbook, I didn't change the formatting settings of the work that had already been done other than the years axis to bring more of the years into the view. It's easy enough to change that background chart:
And yes to your last question about adding an additional sheet and doing an additional Cartesian Join. Please see newly attached workbook (V2) which shows the join. All of the above screenshots are from your original version. In V2, some newly calculated fields needed to be done. Lastly, the Number of Senate Seats will show as a Measure because it is a number. Therefore the number of Senate Seats won't display correctly. The trick to displaying it correctly in the view is to change it to a Dimension once it is dragged onto Rows. Below is how it was done in Excel for the spans of time.
Hope that helps! Thx, Don
BlackSenators(v2).twbx 34.2 KB