9 Replies Latest reply on May 8, 2018 7:29 AM by Okechukwu Ossai

    Calculate End Date

    Darren Lewis

      I have a log from which I am trying to create an activity duration Gantt style chart. As such, I am looking to calculate the Start, End and Duration of the sessions for each user. I have 3 fields available: user id, datetime stamp, event code. Every session starts with the event code of “STRT”, so it is easy to find the Start of the session. However, there is no specific event logged when the session ends. So, in an ascending list of the date/time stamp, the date/time stamp of the event directly before the next “STRT” is the End of the session. For an individual user, the next “STRT” event may be the next day or week or month…there is no pattern. How can I get the end date per session per user id? Attached is as far as I’ve gotten although I've tried a dozen or more derivation of the calculations. I would like one row per user id. Any help or guidance would be appreciated.

        • 1. Re: Calculate End Date
          Shinichiro Murakami

          HI Darren,

           

          It's quite difficult to imagine your expected view, but based on your description, is it like this?

           

          You only need "Start Date" to calculate / visualize your view, because any other event does not have any duration.

           

          Then Unique Event identifier is "Time stamp" as color

           

           

           

           

          But pretty uncertain this is what you need???

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Calculate End Date
            Darren Lewis

            Hello Shinichiro,

             

            Thank you for looking at my questions so quickly. I am looking for a Gantt chart, that is true. Each activity session for each user starts with the date/time of a STRT event. That session ends with the last date/time stamp before the next STRT event. So the duration that would show on a Gantt would not be continuous but fragmented with gaps between sessions. The end date for each session is needed to get the proper duration to place on the size mark. See image below. Any ideas? The date/time showing in Session End calculation now are incorrect for that row. Ideally, I'd love to have this without having to do a table calc.

             

            • 3. Re: Calculate End Date
              Shinichiro Murakami

              HI Darren,

               

              I don't quite understand meaning of your data and your expectations, but as long as I observed your data, the result becomes like this.

              And there is no way to do that without using Table calc unless you do something on data itself with other applications.

              Sorted by start time and each duration is start to next start.

              Then re-sort with User ID.

               

               

              Thanks,

              Shin

              1 of 1 people found this helpful
              • 4. Re: Calculate End Date
                Okechukwu Ossai

                Hi Darren,

                 

                This seems to be a tough one to do without table calcs. I've got some ideas but can't implement it right now since I can't open your workbook (version 2018.1).

                I'll send you a solution to look at when I get home this evening.

                 

                Ossai

                • 5. Re: Calculate End Date
                  Okechukwu Ossai

                  Hi Darren,

                   

                  I've looked at the dataset and have a few questions.

                  • How do you define a session?
                  • Are sessions defined per user or per event code? So, do you just need 1 session start date and 1 session end date per user or is it per user per event code?
                  • There are 10 event codes, are they all equally important? Are there any logic from some event codes which will affect how the session start and end dates are calculated?
                  • Looking at the image you posted in response to Shin, is it okay to assume that OPEN and DONE event codes should be disregarded?
                  • There are users (e.g 214862731) with just STRT and OPEN event codes, how will you want to handle such cases?

                   

                  I thought I should seek clarification on the points above to make sure any solution I provide will meet your requirements.

                   

                  Ossai

                  • 6. Re: Calculate End Date
                    Darren Lewis

                    Hello Ossai,

                     

                    First of all, thank you for taking a look at my question. A few of your questions I think are answered in the original text of the post (see above) but I may not have been as clear as I could. To that end, I made a diagram of a single user with multiple sessions. See image below. You can see that each session has a start event, but the "end" event code varies. That's what makes this exercise challenging. Thanks again for any guidance you can offer.

                     

                    • 7. Re: Calculate End Date
                      Okechukwu Ossai

                      Hi Darren,

                       

                      This one has slipped a few days. Tableau is not able to assign Session End dates and move them to the appropriate rows due to the structure of your data. I got very close but the table calculations were so complex and nested until everything stopped working. This limitation is more related to your data than the software. The best option is to add more attributes to your data outside of Tableau.

                       

                      I assume that this is an Excel data source, so I have written a simple VBA script to provide a basic solution for you. I have counted the sessions sequentially from Session 1 to 873. You can edit the VBA code by adding more loops if you'd rather have the session names restarted every User Id.

                       

                      Some session durations are so small that complete detail is lost if duration is calculated in days. So, I calculated duration in seconds and converted back to days. You can choose to build the Gantt chart in seconds if you wish. I have added a date slider filter to zoom in to some of the small details.

                       

                      I have also attached both the Tableau workbook and Excel workbook containing the VBA code. In the first Excel worksheet, press Alt + F11 to view the VBA code.

                       

                      Hope this helps.

                      Ossai

                       

                       

                       

                       

                      1 of 1 people found this helpful
                      • 8. Re: Calculate End Date
                        Darren Lewis

                        Hi Ossai,

                         

                        Wow! This is great. And a lot of work. I really appreciate it. Very helpful. Thank you so much for looking at this.

                         

                        Best,

                        Darren

                        • 9. Re: Calculate End Date
                          Okechukwu Ossai

                          You're welcome Darren. Glad it helped.