1 2 Previous Next 21 Replies Latest reply on Dec 22, 2019 12:27 PM by Soumitra Godbole Go to original post
      • 15. Re: Time series/total time someone was present at the counter
        Soumitra Godbole

        Are you not able to download the attached twbx file ? In my earlier post at the

        bottom, simply click on the file below and save as.

         

        Untitled.png

        Also I am not getting your value of 4min 23 sec. I suspect my data might have been

        wrongly copied from what you shared (image file). I wish you had uploaded an excel

        or text file instead. With age, the vision does decline and I may have copied the data

        incorrectly, which is possibly why my total time value is  2 min off.

         

        Anyway here is what I have (from the excel file)

         

        pic missing.png

         

        Let me know if the data looks right and also hope you are able to

        download the twbx file. Thanks !


        Sincerely,

         

        Soumitra

        • 16. Re: Time series/total time someone was present at the counter
          Lipi Soni

          Unfortunately, I cannot see the attached twbx file and also cannot find an option to upload an excel. I am new to this forum, sorry for so many questions.

          1 of 1 people found this helpful
          • 17. Re: Time series/total time someone was present at the counter
            Lipi Soni

            Hi Soumitra,

             

            Thank you so much for help.  One more request, how do I sum the total time (all the blue bars) on the chart for each zone by hour? That would give me the total time someone was present in a particular zone for that hour.

             

            This is how the chart looks like.

            sample.png

            1 of 1 people found this helpful
            • 18. Re: Time series/total time someone was present at the counter
              Lipi Soni

              With this: Analysis  > Totals > Show Column Grand Totals It does not consider the overlap time if two people were present at the same time. It just sums all the time difference.

               

              I need, total time counter was covered and total time dinning was covered or someone was present.

              • 19. Re: Time series/total time someone was present at the counter
                Soumitra Godbole

                Hi Lipi,

                 

                   First of all, my sincerest apologies for your earlier  problem as I was tied up

                with a lot of work, music etc. and health issues. In addition to solving problems,

                I started writing lyrics to classic old tunes (related to solving the problem).

                 

                Actually your problem was not at all hard but I was not too sure of your data as

                I remember making some corrections. Fortunately your final dataset seemed right,

                so here is the final solution for your earlier problem.

                 

                Dashboard.png

                 

                Since you have been very patient, I added a bonus feature,which now gives you:

                 

                1) The Total Time someone (at least one person, could be several) was at the counter/dining.

                2) It also gives the % of Time it was occupied (at least one person was at the counter/dining).

                 

                To arrive at your solution I used the popular Date Scaffolding (inserting missing values) by joining

                your dataset with a column showing Time(s) from the start of the first event till the end of the last

                event. I added the extra tab in your excel sheet and have attached the spreadsheet along with the

                twbx file below.

                 

                Dataset (2 Tabs):

                Dataset.png

                Doing a Data Join:

                Here we simply do a Cartesian product data join (m x n rows).

                 

                Data Join.png

                 

                 

                Calculated Fields:

                The advantage of this technique is that we had to create a minimum number of calculated fields.

                Here are the 4 fields that helped to achieve the above dashboard. I thought of simplifying this

                and came up with a simple trick of using seconds after start instead of the Arrival or Departure

                time (similar to Relative time eg:- 3 sec after event 1).

                 

                Formula 1.png

                 

                Formula 2.png

                 

                Formula 3.png

                   This was a very important filter field as our join gave us many unnecessary rows. What this field

                does is show us only those time values in seconds which lie within the arrival and departure time

                for each employee and Zone ID.

                 

                Formula 4.png

                Here we are dividing the Total time spent at Counter/Dining by the difference between the maximum

                and minimum time for each zone ID.

                 

                I have attached the twbx file and excel spreadsheet below. Hope this is helpful and do let me know

                if you have any questions or need further clarity regarding any calculated fields. Also in case this

                answers your question, please mark as helpful, correct answer. Thanks !


                Sincerely,

                 

                Soumitra

                • 20. Re: Time series/total time someone was present at the counter
                  Soumitra Godbole

                  Hi Lipi,

                   

                      I hope you were able to go through the most recent solution. The answer I got was exactly

                  383 s  for the Counter i.e. 6min 23sec (not 4min 23sec as you had mentioned). Please check

                  and confirm. Thanks !


                  Sincerely,

                   

                  Soumitra

                  • 21. Re: Time series/total time someone was present at the counter
                    Soumitra Godbole

                    Hi Lipi,

                     

                        It been a few weeks and I was doing a follow-up to check if you went through my most

                    recent solution to your problem. Unlike the earlier solution, this technique precisely (seconds)

                    determines the time when at least one person was at the counter or at dining.

                     

                        Also once you have confirmed, please unmark the earlier solution and mark this solution

                    as the correct answer as it is definitely way better than the earlier one.

                     

                    Thanks !

                     

                    Sincerely,

                     

                    Soumitra

                    1 2 Previous Next