13 Replies Latest reply on Oct 24, 2018 3:49 PM by adam jama

    Average order per hour

    adam jama

      Hello,

      Im just starting with tableau public and I am trying to get the average number of orders per hour.

      How can I list hours on the viz? (from 12-1am ; from 1am-2am etc)

      I was able to find a way to create a calculated field with all the weekdays which was super helpful and id like to have the same thing listed but for hours.

      I created a set to include all orders from 12 - 1pm but I cant figure how to calculate the average.

      Here's the link to the tableau viz:  https://public.tableau.com/profile/adam2629#!/vizhome/totalordersbetween12and1pm/Sheet13

      Thanks in advance!!

        • 1. Re: Average order per hour
          Don Wise

          Hi Adam,

           

          See if the below works for you?  Here's the link to my Tableau Public site: Tableau Public  I would attach the workbook here, but you won't be able to open it in Tableau Public.

           

          You'll need to download from my public site in order to work with it. Hope it helps and answers your question.  Thx, Don

          Screen Shot 2018-10-19 at 1.45.03 PM.png

          I created Time Bins for you:

          Screen Shot 2018-10-19 at 1.45.35 PM.png

          1 of 1 people found this helpful
          • 2. Re: Average order per hour
            adam jama

            Hi Don,

            Thank you so much for taking the time to help me out with this. The work you did is exactly what I was looking for. It will help me for many questions I have!! Could you tell me how you did it? To what I understand you created a calculated field? I've created a calculated field and copied and paste your calculation which worked fine for the workbook where the time bins dimensions already existed. However when I try to add it to my original workbook I have an error message saying: DATEPART is being called with (string.string) , did you mean (string.datetime)?

            Besides the new calculated field to create the bins did you do anything else? I've watched tutorials on creating bins but that doesnt seem to be what you did?

            Thanks for your help!

            Adam

            • 3. Re: Average order per hour
              Don Wise

              Hi Adam,

               

              Glad to have helped!

               

              You'll need to change your time dimension to be from a string to a Date/Time value as shown and that should take care of the issue.  Simply click on the icon next to Order Time PST and change from a String to Date & Time:

              Screen Shot 2018-10-19 at 3.44.58 PM.png

              Thx, Don

              1 of 1 people found this helpful
              • 4. Re: Average order per hour
                Don Wise

                To answer the other part of your question...the calculation if you read through it...is simply forcing Tableau to look at the 'hour' part of your time field and if it matches between the upper and lower limit, put that order time value into that particular bucket. Then use an LOD to average the results, which wasn't much different from what you already had (same result) !

                1 of 1 people found this helpful
                • 5. Re: Average order per hour
                  adam jama

                  It works!! Thanks Don!!

                  • 6. Re: Average order per hour
                    adam jama

                    Hi Don,

                    If I may ask you another question.

                    The times in my data are in AM/PM but when I calculate the average its not reading the AM/PM.

                    The line of the calculation below could show orders between 1am-2am as well as 1pm-2pm.

                    ELSEIF DATEPART('hour',[order time])>=1 AND DATEPART('hour',[order time])<2 THEN '1pm-2pm'

                    How can I convert my time into military time so that an order placed at 10:13pm shows 2313 instead?

                    Thanks in advance for your time and help.

                    • 7. Re: Average order per hour
                      Don Wise

                      Hi Adam,

                      The line of code you're referencing is only for 1am-2am.  In Tableau that is written explicitly in military time >=1 and <=2 for the 1am and 2am hour period.  For 1pm-2pm, it is the subsequent line of code explicitly written as >=13 and <=14. Hopefully that makes sense?

                       

                      That particular calculation [Order Time Bins] will not format into 24-hour format simply by formatting the calculation; it is not a date/time field, but a dimension. You'll need to go into that calculation and change each instance after 12 to the two-digit military time as appropriate.  As example:

                       

                      ELSEIF DATEPART('hour',[Order Time (PST)])>=12 AND DATEPART('hour',[Order Time (PST)])<13 THEN '12pm-1pm' would be changed to:

                      ELSEIF DATEPART('hour',[Order Time (PST)])>=12 AND DATEPART('hour',[Order Time (PST)])<13 THEN '12:00-13:00'

                       

                      Etc.

                       

                      However, If you want to use your other time fields to be formatted as military time, it's relatively simple to do that:

                      Click the down arrow on your Time Field and select Format:

                      Screen Shot 2018-10-24 at 10.48.19 AM.png

                      A window pane will open to the left, then select Dates, then set the time under Custom to be formatted as CAPITAL HH:mm:ss which sets the hour to Military Time:

                      Screen Shot 2018-10-24 at 10.53.09 AM.png

                      1 of 1 people found this helpful
                      • 8. Re: Average order per hour
                        adam jama

                        Hi Don,

                        Thanks for your answer.

                        According to the workbook below, I'm getting many orders in the morning (1am-2am etc).

                        However, I'm not open at that time so it's definitely orders placed in the afternoon.

                        When I look up the data of these hours it does say am as well which is strange. So I'm guessing I did something to change it to am?

                        Tableau Public

                        Thanks for your help.

                        • 9. Re: Average order per hour
                          adam jama

                          Another question if you dont mind: all the times are shown as a strange date "12/301899 9:16:00am"

                          Is is possible to have just the time and no date next to it?

                          • 10. Re: Average order per hour
                            Don Wise

                            Hi Adam,

                            It probably has much to do with how your data is/was originally structured. As I recall, in order to get things to work properly, your Order Time was a string, meaning it wasn't recognized by Tableau as either a date/time. Tableau is very generous about reformatting such data as date/time if it recognizes the structure as being able to support that type of change, which is what I did, changed it from String to Date/Time.

                             

                            When that happens, Tableau will supply your system date next to the time value.  In your case it displays as 12/30/1899 hh:mm:ss and in my case it displays as 1/1/1900 hh:mm:ss.  I believe if you use that formatting demonstrated prior, it should remove the date just leaving the time. 

                             

                            Thx, Don

                            1 of 1 people found this helpful
                            • 11. Re: Average order per hour
                              adam jama

                              Thanks so much Don!

                              • 12. Re: Average order per hour
                                Don Wise

                                Hi Adam,

                                Best thing to do to fix your issues is to have whomever is providing your data to you - provide the time in a true time format, not a string.  I just looked at your data a bit more deeply and tried a few known calculations to convert time coming into Tableau into true date/time or time values and didn't get anywhere with it.

                                 

                                So...leads me to believe the underlying data needs some work before bringing into Tableau. It also might resolve what you're seeing with orders showing as 1AM instead of 1PM....just my thoughts...Thx! Don

                                1 of 1 people found this helpful
                                • 13. Re: Average order per hour
                                  adam jama

                                  Thanks Don, Im downloading the data myself through the point of sale system in excel format.