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.
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)
Let me know if the data looks right and also hope you are able to
download the twbx file. Thanks !
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.
report1.0.xlsx 9.0 KB
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.
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):
Doing a Data Join:
Here we simply do a Cartesian product data join (m x n rows).
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).
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.
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 !
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 !
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.