4 Replies Latest reply on Feb 23, 2015 4:51 AM by Mark Fraser

    Date / Time Analysis, newbie user.

    Mike Coutts

      Hey all,

       

      I'm a student doing a project that is date/time heavy.

      The format that I have is unorthodox, meaning it's not an excel format.

       

      What I'm trying to do is group the data by Day (e.g. Sunday, Monday, Tuesday) and by hour block (e.g. 1am, 2am, 3am, etc...).

      The goal is determining what day we get the bulk of our calls on and the timeframe they fall in.

       

      In Tableau, when I tell the system that the Disp. Date/Time field is exactly that, the data turns to null. I'm not sure why. It's a string of data by default. So really the only field I care about is Disp. Date Time, but I can't figure out how to get it split and into a more usable form. Any help would be awesome!

       

      Here is a sample of the data:

      PRID  Dispatch ID  Disp. Date/Time  Unit  Time 
      3375860515000101/01/15 00:36   33933.000
      3375939215000201/01/15 07:09   33941.000
      3376021015000301/01/15 08:47   33941.000
      3376201515000401/01/15 09:56   33941.000
      3376241515000501/01/15 11:15   33942.000
      3376310915000701/01/15 14:21   33943.000
      3376491715000801/01/15 16:28   33944.000
      3376520715000901/01/15 17:01   33939.000
        • 1. Re: Date / Time Analysis, newbie user.
          Shine Pulikathara

          Hi Mike,

           

          Have you taken a look at the Dates section on this page already? You might find some tips there.

          Common Questions on the Tableau Forums

           

          I copied the sample data table into Tableau, and it recognized the dates/times automatically.

          • 2. Re: Date / Time Analysis, newbie user.
            Mark Fraser

            Hi Mike

             

            It may not be perfect, but are you after something like the attached?

            I have used your example data, aggregated time by weekday and hour... split by unit.

             

            Hope it gives you a starting point...

             

            Cheers

            Mark

            • 3. Re: Date / Time Analysis, newbie user.
              Mike Coutts

              Hey Mark,

               

              Thanks for reaching out, I appreciate it.

               

              I'm really focused on that Disp. Date/Time column.

              What I'm trying to trend over a few years is when we receive the bulk of our calls, e.g. Monday between 10-11am.

               

              Since the data is in one column I'm having a hard time trying to bucket everything.

              I think what I'll try doing in excel is splitting the date/time so

              Column A = Date

              Column B = Time

               

              Then create a new column C that is the Day of the Week, and try to trend it that way. If you have a better approach, I'd love to hear it.

              • 4. Re: Date / Time Analysis, newbie user.
                Mark Fraser

                Hi Mike

                 

                I agree with your approach, sounds reasonable to me.

                Have you opened/ checked my workbook? I have started you off on this path...

                 

                Disp. Weekday: datename('weekday',[Disp. Date/Time])

                by selecting only the weekday we can bucket a 1 year of data (for example) into just those 7 days, all 52 Monday's will appear as a single line.

                 

                Disp. Hour: datepart('hour',[Disp. Date/Time])

                as above, but now splitting the hour from the date, use as a column

                 

                in the above/ attached I have also split by Unit although I'm not sure if that is required.

                I can see that Thursday at 5pm is your busiest (assuming I should sum time) (for unit 3393)

                and Thursday at 4pm for unit 3394

                 

                does that make sense? am i understanding what you're trying to do?

                 

                Cheers

                Mark