You'll want to make sure that you have a date column (without the timestamps) and then you can add this formula:
- Hours Not Booked: max(24)-sum([Hours Booked])
See attachment that uses your sample data above.
Room Bookings.twbx 25.4 KB
Thanks very much for your reply.
I’m unable to lead the workbook. Can you upload it again?
If you are not using Tableau 9, you won't be able to open the workbook. Instead, I attached a screenshot.You should be able to replicate it with the information below.
- F1 is FrameStartTime in the your sample data
- F2 is FrameEndTime in your sample data
- Date= makedate(year([F1]),month([F1]),day([F1]))
- **Note: this will only show dates for the days when the room has been booked. However, you might want to include all days the room is available-- in this case you will need to add a new date field into your data source. On the days when no rooms are booked the FrameStartTime and FrameEndTime values will be null.
- Hours Booked = DATEDIFF('second',[F1],[F2])/(60*60)
- Hours Not Booked = max(24)-sum([Hours Booked])
Screenshot.PNG 38.9 KB
I might not have been asking my question right.
My problem is to find the total sum of hours not booked.
As you see from my screen print the same day will have multiple rows, and hence “#hours available per day” will sum for each row and end up with >24hours booked on a single day.
How do I get Tableau to recognize that it needs only calculate this for a given date and not for each row?
Thanks again ☺
image001.png 23.1 KB
Sarah's formula works when only one row is shown per date.
Since you want to show more details, you could use a running sum table calculation like this:
You will need to edit the scope of the table calculation to:
- Edit Table Calculation
- Compute Using: Advanced
- Addressing: Date, F1, F2
- Restarting Every: Date
I hope this works.
Johan aka Katten
Attached Workbook Version: 9.0
thread 170508 Room Bookings.twbx 51.6 KB
Thanks! That is great. It solved the sum-issue.
However, I’d like to show the sum per Date only, so I need it to show only the last row for each Date.
Such that the amount of hours available for 08-01-2014 is 19,50. And for 09-01-2014 it shows the 14,50…and so on.
The purpose is to be able to do a bar chart with the amount of hours not booked on a given Date.
How should I create a calculated field to solve this?
I’ve tried different max() referring to F2 but I’ve not been able to solve it.
Thanks and regards,
image001.png 66.6 KB
What Tableau version do you use? I have all versions between 4.0 and 9.0 installed :-)
I ask, because it is easier to communicate "by attached workbooks" and therefore prefer to use your version.
Also, it seems Sarah's formula per day is the one to use.
Ps. Here is a screenshot of my Windows 7 desktop shortcuts:
I mention this to make in particular helpers conscious about that it is no big deal to have many version installed simultaneously. The only important thing is to (re)install the version one uses most often last so it becomes the default Tableau version.
I’ve got the 8.2 version
The attached uses Sarah's formula and hope it helps you closer to what you need:
See more in attached workbook, which is made in Tableau 8.2.
Attached Workbook Version: 8.2