7 Replies Latest reply on Dec 9, 2016 2:13 PM by Shinichiro Murakami

    Space utilization/Finding times within ranges

    Joshua Thomas

      Hello-

       

      I am somewhat new to actually using Tableau, although I have been to quite a few classes and TC15 and TC16. I recently tried to take on space usage analysis project using Tableau before I realized I was a bit overconfident in my skill and knowledge of Tableau.  I had to bail back to Excel and found I had to even research there to figure this out.

       

      I attached my Excel analysis about which I have 3 questions.

      1) On the Hours in Range column, how would you do that same calculation in Tableau?  Essentially, there is a parameter range in A2 and B2 that provides the start and end time to be considered.  It should work such that if the range was set to 8am-5pm, and the event was from 7am-830am, then only 30 minutes should be considered in the utilization.

      2) I use Excel's networkdays calc in my "hours in range" column calculation, which was the specific requirement of this analysis.  However, I could see a future use-case of allowing the viz consumer to select whether weekends should be included or not.  What approach would you take to accomplish that?

      3) When I attempted to use DateDiff in Tableau, my calculation was always off by a fraction of an hour up to a whole hour.  The same duration calculation in Excel ([reservation end time]-[reservation start time]) provides the answer I would expect.  What do I not know about DATEDIFF and times in tableau that is affecting this calculation?

       

      If there is any other advice for analyzing this in Tableau, I would be most grateful.  I would much rather create a Tableau viz than do this in Excel.

       

      Thanks for your help.

      Josh

       

      Message was edited by: Joshua Thomas to clarify question in #2.

        • 1. Re: Space utilization/Finding times within ranges
          Shinichiro Murakami

          Hi Jpshua,

           

          It took quite a bit, but I think I finally have answer.

           

          Whichever the tool is, it's quite complicated calculation.

          I gave up to analyze your excel formula, but anyways create same functioning formula in Tableau.

           

           

          Several components.

           

          Normal calc

          1) Calculate the First Day Hours based on range hours controlled by parameter

          2) Calculate the Last Day Hours based on range hours controlled by parameter

          3) Calculate days between excludes first and last day

          4) #3 x daily hours from parameters

          5) calculate total hours

           

          Weekend Calc

          1) Judge the first day and the last day is weekend or not

          2) Calculate weekend days between start and end

          3) Calculate Weekend hours by #2 x  hours from parameters

           

           

          Normal calculation

          [Event Time Range Start]

          datetime(date([Reservation Start Date])+[Start Time]/24)

           

          [Event Start Calc]

          if [Reservation End Date]<[Event Time Range Start] then Null

          else max([Reservation Start Date],[Event Time Range Start])

          end

           

          [Hours First Day]

          (min([Even End  Calc],datetime(date([Reservation Start Date])+[End Time]/24))-[Event Start Calc])*24

           

          [Event Time Range End]

          datetime(date([Reservation End Date])+[End Time]/24)

           

          [Even End  Calc]

          if [Reservation End Date] <[Event Time Range Start] then Null

          else min([Reservation End Date],[Event Time Range End])

          end

           

          [Hours Last Day]

          if date([Reservation End Date])>date([Reservation Start Date])

          then ([Even End  Calc]- datetime(date([Reservation End Date])+[Start Time]/24))*24

          else 0 end

           

          [Days in between]

          max(0,date([Reservation End Date])-date([Reservation Start Date])-1)

           

          [Hours including Weekend]

          [Hours First Day]+[Hours Last Day]+[Days in between]*([End Time]-[Start Time])

           

           

          Weekend Calculation

          [Event Start Day +1]

          MIN(date([Reservation Start Date]+1),DATE([Reservation End Date]))

           

           

          // Make Start day aligned to Friday (Only Sat,Sun is set as last Friday, other = future Friday)

          [Event Start+1 First Friday] 

          date(datetrunc('week',[Event Start Day +1]+4,"Friday"))

           

          [Event Date End -1]

          max(DATE([Reservation Start Date]),date([Reservation End Date]-1))

           

           

          // Make End day aligned to Monday (Only Sat,Sun will be Next Monday, other = past Monday)

          [Event End-1 Last Monday] 

          date(datetrunc('week',[Event Date End -1]+2,"Monday"))

           

          [Days of weekend]

          min(

          [Days in between],

          (floor(([Event End-1 Last Monday]-[Event Start+1 First Friday])/7)+1)*2

              -if datename('weekday',[Event Start Day +1])="Sunday" then 1 else 0 end

              -if datename('weekday',[Event Date End -1])="Saturday" then 1 else 0 end

          )

           

          [Hours Weekend]

          [Days of wekend]*([End Time]-[Start Time])

          + if datename('weekday',[Reservation Start Date])="Saturday"

          or datename('weekday',[Reservation Start Date])="Sunday"

          then [Hours First Day] else 0 end

          + if datename('weekday',[Reservation End Date])="Saturday"

          or datename('weekday',[Reservation End Date])="Sunday"

          then [Hours Last Day] else 0 end

           

          [Hours In Range Calc]

          [Hours including Weekend]-[Hours Weekend]*[Include Weekend]

           

           

          I verified potential variety of Dates with another data between Tableau Calc and your excel Calc.

          So I believe the formula works exactly same as your excel formula.

           

           

          Thanks,

          Shin

           

           

          1 of 1 people found this helpful
          • 2. Re: Space utilization/Finding times within ranges
            W T

            Hi Shin,

             

            This looks great.  However, I'm currently not able to open the workbook.  What is the difference between the Reservation Start Date and Start Time? 

             

            Thanks

            1 of 1 people found this helpful
            • 3. Re: Space utilization/Finding times within ranges
              Shinichiro Murakami

              W T

               

              I don't know, 

              But in original excel, two measures are exactly same.

              Are you using older version of Tableau?

              Actually I shared the concept at this link.

               

              EXCEL's "networkdays" s alternative

               

              Thanks,

              Shin

              1 of 1 people found this helpful
              • 4. Re: Space utilization/Finding times within ranges
                Joshua Thomas

                Hi Shin

                 

                Thank you so much.  I am still trying to process and play with your TWB to understand what is going on.  I think its the correct answer, but just want to understand a little more about your approach.  I like your networkdays explanation as well.

                • 5. Re: Space utilization/Finding times within ranges
                  Shinichiro Murakami

                  Don't worry.  Take your time.

                   

                  Thanks,

                  Shin

                  • 6. Re: Space utilization/Finding times within ranges
                    W T

                    Are they both calculated fields?  or do you mean they are both the exact same date field?  The Reservation Date and Start Time...I can see the Reservation Date is most likely your Start/From Date, but don't know what "Start Time" is...I'd like to try to build the Event Time Range Start calculated field as you directed, but have no clue what Start Time is to build the calc

                    • 7. Re: Space utilization/Finding times within ranges
                      Shinichiro Murakami

                      You don't need to worry about the difference between two fields.

                      Just say simply, reservation and start is same in original data set.

                       

                      if "=" is shown on left side, it's calculated field. If not, it's included in original data.

                       

                       

                      Thanks,

                      Shin