9 Replies Latest reply on May 15, 2015 7:05 AM by kettan

Sum time intervals on a date placed over multiple rows i data?

The table show time slots booked for a room.

For some dates the room has been booked several times, like 12-16 and then 20-21:30.

Booking times:

 08-01-2014 12:00 08-01-2014 16:00 08-01-2014 19:00 08-01-2014 19:30 09-01-2014 07:30 09-01-2014 15:30 09-01-2014 20:00 09-01-2014 21:30 10-01-2014 20:00 10-01-2014 21:30 11-01-2014 17:00 11-01-2014 18:30 13-01-2014 07:30 13-01-2014 22:30 14-01-2014 12:00 14-01-2014 16:00 15-01-2014 12:00 15-01-2014 16:00 15-01-2014 20:00 15-01-2014 21:30 16-01-2014 12:00 16-01-2014 16:00 16-01-2014 20:00 16-01-2014 21:30 17-01-2014 12:00 17-01-2014 16:00 17-01-2014 20:00 17-01-2014 21:30

These bookings are placed in different rows so when I want to calculate the number of hours NOT booked I get a wrong result.

To calculate the number of hours booked I've used the following formula:

(DATEDIFF('second',[FrameStartTime],[FrameEndTime]))/(60*60)(DATEDIFF('second',[FrameStartTime],[FrameEndTime]))/(60*60)

It appears to calculate for each row, and not for each date. This is fine when I want to sum but how do I find the available time (NOT booked) on a given date?

Best Regards,

Tine

• 1. Re: Sum time intervals on a date placed over multiple rows i data?

Hi Tine,

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.

Sarah

• 2. Re: Sum time intervals on a date placed over multiple rows i data?

Hi Sarah

Thanks

/Tine

• 3. Re: Sum time intervals on a date placed over multiple rows i data?

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])

Sarah

• 4. Re: Sum time intervals on a date placed over multiple rows i data?

Hi Sarah

Thanks again.

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 â˜º

Regards,

Tine

• 5. Re: Sum time intervals on a date placed over multiple rows i data?

Hej Tine,

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:

```MAX(24)-RUNNING_SUM(SUM([Hours Booked]))

```

You will need to edit the scope of the table calculation to:

1. Edit Table Calculation
4. Restarting Every:  Date

I hope this works.

Johan aka Katten

Attached Workbook Version:  9.0

• 6. Re: Sum time intervals on a date placed over multiple rows i data?

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,

Tine

• 7. Re: Sum time intervals on a date placed over multiple rows i data?

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.

.

• 8. Re: Sum time intervals on a date placed over multiple rows i data?

I’ve got the 8.2 version

• 9. Re: Sum time intervals on a date placed over multiple rows i data?

The attached uses  Sarah's  formula and hope it helps you closer to what you need:

```max(24)-sum([Hours Booked])
```

See more in attached workbook, which is made in Tableau 8.2.

Attached Workbook Version:  8.2

.