1 Reply Latest reply on Jan 15, 2016 12:52 PM by Zach Leber

    calculate "busy days" of a resource based on transaction or lookup table

    Stephan Lorenz

      Dear all

       

      I am new to Tableau and this forum, so please bear with me if I seem a little bit slow in understanding some of the answers you might give me.

       

      This is my current challenge:

       

      I have a booking calendar for various instruments that records users, the resource they book, some more details and the start and end times as a lookup table. I want to calculate the number of days a resource has been booked over a period of time and display 'busy days' in the last 3 months, both as bar chart and line-chart over time.

       

      There are 3 scenarios:

      a) a resource gets booked once on a day, so that is 1 busy day - one would just COUNT the occurences of that resource

      b) a resource gets booked multiple times a day, which still is 1 busy day - COUNTD gives the correct number (1)

      c) a resource gets booked for multiple days (start and end date on different days), which can be calculated with DATEDIFF to give the number of booked days

       

      However, all 3 scenarios are present in my dataset and I cannot combine COUNTD + DATEDIFF since one is aggregated and the other one isn't. I thought that a transaction table might help me solve this. After a bit of messing around with Custom SQL I now have the same data as a transaction table that contains a [Date] and [Type = Start/End] column, but from there on I am stuck. Maybe this approach is wrong altogether... Can someone please help?

       

      Thanks

      Stephan

        • 1. Re: calculate "busy days" of a resource based on transaction or lookup table
          Zach Leber

          Hi Stephan, you're on the right track, but this is tricky in Tableau. Converting your raw start/end data into reshaped date/delta data is key. Then you can sort on date and plot a running sum of delta where delta is the number of open tickets/requests on that day. For your case I'm adding +1 to your End date so if start=end you still show that day as busy. Here's the algorithm, an image of the results, and an 8.1 packaged workbook. It uses Custom SQL with an Excel data source so won't open on Mac. It should work with both multiple and overlapping reservations. I don't know how to show the final value without showing the individual days that set up the table calculations in the first place. Putting the answer in the caption is one way to show a table calc for a view.

           

          SELECT [Sheet1$].[Start] AS [Date],

            +1 AS [Delta]

          FROM [Sheet1$]

           

          UNION ALL

           

          SELECT [Sheet1$].[End]+1 AS [Date],

            -1 AS [Delta]

          FROM [Sheet1$]

           

          BusyDays.png