13 Replies Latest reply on Sep 4, 2016 11:20 PM by Fikri Shihab

    Time interval

    Fikri Shihab

      Hi Tableau community,

       

      I have  source file that has several columns and the data represent network activity log based on time series. Date and time column are separated in a separate column.  I'd like to make  a dynamic time interval for my dimension to show number of users (y axis) on time series (x axis). For now, it is fine for me to 15 min, 30 min or 1 hour interval. How should I do this? 

       

      I am very new with Tableau.

       

      Thanks for help.

        • 1. Re: Time interval
          Fikri Shihab

           

          I did try calculated filed to create 30 minutes bin time interval using

           

          Datetime(round(float([Time])*48,0)/48)

           

          but nothing showed up on x- axis (dimension). I checked the time format (this filed has no date component) is string, which presumably the correct format.  Any suggestion?

           

           

          • 2. Re: Time interval
            Kathryn Bridges

            Hi Fikri,

             

            Could you post your Tableau workbook? Thanks!

            • 3. Re: Time interval
              Fikri Shihab

              HI Kathryn,

               

              Sorry for late reply. Please find attached the workbook.

               

              Thanks!

              • 4. Re: Time interval
                Kathryn Bridges

                Hi Fikri,

                 

                Sorry- I'm a little stumped here. Maybe someone else can jump in and help here as well. My initial idea was to create a integer variable for your time stamps, and then bin them. While this kinda works, I quickly realized the limitations of bins.

                 

                Mainly, that:

                a) They cannot be used in if/then or case/when statement to allow a parameter to switch between 15/30/45 minute bins

                b)They cannot be used to 'back-calculate' a bin time to put on the axis.

                 

                The trend in this workbook is probably what you are looking for for something like the 15 minute interval/CountD Customer ID, but doesn't really serve well for your time variable. sorry!

                 

                I also found this article by Richard Leeke, which references something like the equation it looks like you were going for above. Maybe it will help as well?

                Datetime Bins

                • 5. Re: Time interval
                  Fikri Shihab

                  I followed exactly what the article explains in the calculation column but the result shows null and unable to generate time buckets. The only difference is the date and time in my date is in separate column instead of a full time stamp. When I applied the bin calculation to the time column nothing showed up.  This is probably basic stuff and may be relayed to the format but just can't figure out why - still very new to Tableau. 

                   

                  Any suggestion from other Tableau experts?

                  • 6. Re: Time interval
                    kettan

                    I am on Tableau 9.0 and couldn't open the attached workbook.

                    I did get access to the csv inside it and made this formula:

                     

                    datetime(int(float([Time]) * [Interval]) / [Interval])

                     

                    Is it something like this you want?

                     

                    thread 214339 Time interval.gif

                     

                     

                    Attached Workbook Version:  9.0

                    • 7. Re: Time interval
                      Fikri Shihab

                      Hi,

                       

                      Yes, something like this, but I'd like to be able to see time for each date, not aggregated for the two days.  Also, I have seen the date in the calculation column still shows 1/1/1900, which is incorrect. I'd like to be able to show a few options on X- axis:

                       

                      - Time buckets disregarding the date to see general pattern of each day

                      - Time buckets with date, for instance two days worth of data and with 30 minutes interval - it should show the count continuously for these 48 hours with desired interval (e.g. 30 minutes or 1 hour).

                      - Time buckets (24 hours) with different line/bars for each day

                       

                      Any idea how this can be done?

                       

                      Thanks!

                      • 8. Re: Time interval
                        Richard Leeke

                        I've attached a workbook showing how to do each of those options.

                         

                        The main thing is to combine the [Date] and [Time] columns to get yourself a datetime to work with. There are various ways of doing that, but often the easiest is to combine the source Date and Time columns into a single string in a format that Tableau can automatically convert for you, which is what I have done here.

                         

                        [Datetime] = DATETIME([Date] + " " + [Time])

                         

                        I noticed that there were a few odd rows (below) in your original CSV file, so I filtered those out - except in sheet "Dodgy data".

                         

                        03/01/1900,,4,,,AY5,3.2,,,,,,N

                        08/01/1900,,4,,,AY5,3.2,,,,,,N

                        05/01/1900,,4,,,AY5,,,,,,,N

                        05/01/1900,,4,,,AY5,,,,,,,N

                        07/01/1900,,4,,,AY5,,,,,,,N

                        07/01/1900,,4,,,AY5,,,,,,,N

                        05/01/1900,,4,,,AY5,,,,,,,N

                        08/01/1900,,4,,,AY5,,,,,,,N

                        08/01/1900,,4,,,AY5,,,,,,,N

                        • 9. Re: Time interval
                          kettan

                          You could place the Date on Color and filter on the days you want to compare.

                          In the example below Date is set to Day and Discrete.

                           

                           

                          As extra info, the same was dropped on Label to make it easy to see what line is what day.

                           

                           

                          Attached Workbook Version: 9.0

                          • 10. Re: Time interval
                            Fikri Shihab

                            Hi Richard,

                             

                            Many Thanks for showing how to do this.  I now realize that Tableau need both data and time in a combined column 

                             

                            Just have a few questions related to the calculation in the source data in Tableau. In the data source when time only column is to be formatted as date and time, the date shows 01/01/1900 and Time interval calculation (last column) will also show the same. For sure in this case the dates are not correct.  Any reason why Time Only column needs to be formatted with both DateTime?  Is there any time only function in Tableau or can it be formatted as string so that this 01/01/1900 is not shown in the Data source?

                             

                            Furthermore,  how do I show bar chart for Date TIme sheet similar with Sheet One instead of stair like bars and also the dates on the X axis shows 23 July where the data is only up to 22 July. Any clue?

                            • 11. Re: Time interval
                              Richard Leeke

                              Datetimes are held held internally as a number representing the number of days since the "epoch date" which is usually 1st January 1900 (though Excel treats uses 31st December 1899 - it's a long story). The time portion of the datetime is represented by the fractional part of the number - so 0.25 would 6:00 am, 0.5 would be 12:00 pm, etc.

                               

                              So if you only have the time portion, but you format it as a datetime, Tableau believes you are dealing with a time on 1st January 1900.

                               

                              You can set the format for a datetime column to only display the time by right clicking on the TimeOnly pill and selecting Format. Choose the Custom option for Dates and specify a format of "hhmm:ss" (or "hh:mm" if you don't need seconds, etc).

                               

                              You can also suppress the "1 January 1900" in the axis by editing the axis.

                              Just uncheck the Automatic box for the subtitle:

                              That gives this:

                              I presume the stair like bars you are talking about is this, is it?

                               

                               

                              I've never seen that before, I think that must be a Tableau 10 "enhancement". It's pretty horrible isn't it? I do vaguely recall seeing something about a new option to have the widths of bars set automatically - I was intending to use that in some of my stuff. Not sure I will now.

                               

                              You can change it by clicking on the size pill and selecting "Manual" - that gives you a slider to set the bar width.

                               

                              The only trouble with that is that as you change your time interval the bars will either overlap or end up looking too skinny. I thought that was what the automatic width option was supposed to address.

                               

                              Where are you seeing 23 July on the X axis? It only shows up 22 July for me as you can see in the screenshot above.

                               

                              I've attached another version of the workbook with the above changes.

                              • 12. Re: Time interval
                                Richard Leeke

                                I read up on bar widths in Tableau 10 and it does (of course) work much better than it seemed.

                                 

                                If the Size setting is set to "Fixed" it defaults to the unit of the axis. In your case, as the X axis is a date, the default width is one day (which looks very odd when you have one or more marks per hour.

                                 

                                 

                                But you can create a calculated field to give the width that you want. In your case, if you create a field [Bar Width] (say) as 1 / [Interval], that will size the bars correctly.

                                 

                                 

                                Here it is set to one hour.

                                And here set to 30 minutes.

                                 

                                The good thing about this is that it resizes automatically as you resize window, as well as resizing when you change the bucket size for the axis.

                                 

                                By the way - I think this explains why you were seeing dates beyond 22 July. As the bar widths were one day, the last bars fro 22nd July were overhanging into 23rd.

                                 

                                Updated workbook attached.

                                • 13. Re: Time interval
                                  Fikri Shihab

                                  Hi Richard,

                                   

                                  Many Thanks for the solution and explanation, also on the bar chart. This is helpful!