1 2 Previous Next 21 Replies Latest reply on Dec 22, 2019 12:27 PM by Soumitra Godbole

# Time series/total time someone was present at the counter

I want to calculate total time when a employee was present at the counter on 11/20/2019 from 9am-10am. There can be 2 employees during the same time, so we cannot sum the time difference. How do I plot the percentage or total time someone was there at the front counter?

employee     zone               arrival                                        departure

1               Counter          11/20/2019  9:34:52 AM          11/20/2019  9:35:02 AM

2               Counter          11/20/2019  9:29:34 AM          11/20/2019  9:29:46 AM

3               Counter          11/20/2019  9:30:08 AM           11/20/2019  9:25:46 AM

2               Counter          11/20/2019  9:40:34 AM          11/20/2019  9:45:46 AM

3               Counter          11/20/2019  9:37:08 AM           11/20/2019  9:50:46 AM

• ###### 1. Re: Time series/total time someone was present at the counter

Hi Lipi, It sounds like you would be able to do a level of detail calculation to achieve this. It would look something like this:

{ INCLUDE [employee] : DATEDIFF('minute', [arrival], [departure]) }

Hopefully that will do the trick for you!

• ###### 2. Re: Time series/total time someone was present at the counter

Hi Justin,

Thank you for the response, however I am getting this error: result of a level of detail must be an aggregate tableau

• ###### 3. Re: Time series/total time someone was present at the counter

Try wrapping the datediff in a SUM calc: { INCLUDE [employee] : SUM(DATEDIFF('minute', [arrival], [departure])) }

Let me know if that doesn't work for you.

• ###### 4. Re: Time series/total time someone was present at the counter

This gives a sum of total time spent by each employee at counter. It is not considering the overlap, example: A was at the counter from 9-9:30 and B was at the counter from 9:15-9:45. So someone was at the counter for 45minutes and not 1hour.

• ###### 5. Re: Time series/total time someone was present at the counter

Hi Lipi,

Try this : {fixed [Employee], max([departure time] } - {fixed [Employee], max([arrival time] }

Let me know if this helps.

Thanks,

Shruten

• ###### 6. Re: Time series/total time someone was present at the counter

Ah, I see. Would doing DATEDIFF('minute', MIN(arrival), MAX(departure) work for more than just your example?

• ###### 7. Re: Time series/total time someone was present at the counter

Hi Shruten,

Thank you for the reply. This will not consider the time if someone was not there at the counter.

A   Counter   9:00-9:15

B   Counter   9:05 - 9:15

A   Counter   9:20 - 9:30

Total time counter was covered = 25 minutes

• ###### 8. Re: Time series/total time someone was present at the counter

This will not consider the time if someone was not there at the counter.

A   Counter   9:00-9:15

B   Counter   9:05 - 9:15

A   Counter   9:20 - 9:30

Total time counter was covered = 25 minutes

• ###### 9. Re: Time series/total time someone was present at the counter

Hi Soumitra,

Thank you for the reply. However, this gives me total hours for each employee. I want total hours/minutes the counter was covered or someone was present at the counter. Sorry for the confusion and arrival departure mismatch.

• ###### 11. Re: Time series/total time someone was present at the counter

Discard the above image. Use this image as reference, it has time in seconds.

• ###### 12. Re: Time series/total time someone was present at the counter

Hi Lipi,

Thanks for the correct data and explanation of what you need.That changes the story and here

is a simple logical solution, creating a Gantt chart (using min for earliest start event). Also below

the visual, there are 2 simple calculated fields for Delta Time. Hope this helps:

As explained in the visual, Total Time at the counter is the sum total of the time shown in 3

red boxes which comes to 6.367 min or 6min 22sec. I have enclosed the twbx file below and

hope this answers your question or helps someone else to arrive at the solution. Best wishes !

Sincerely,

Soumitra

• ###### 13. Re: Time series/total time someone was present at the counter

Thank you for your help. could you please share the twbx file? What is calculation1 in columns? Also is there a way to just plot zones(y-axis) by hour(x-axis) and total time someone was present for each hour? Simple bar graph.

• ###### 14. Re: Time series/total time someone was present at the counter

Also, total time at counter without the overlap = 4:23

1 2 Previous Next