8 Replies Latest reply on Mar 1, 2019 1:07 AM by Martin Dubucq

# number of users between interval

Hi,

I am starting out tableau as an analyst.

I would like to see the average number of people that are in a shop during the day. So, I would like to see for example that at 8am, there is on average 10 people in the shop.

As such my dataset in google sheets is as followed : Users_name, ID_records, Date start, Date End ( for the start of the sessions and the end, with date and time).

Should I had a column with the interval? Is it even possible with the data I have?

Is there an other topic that could be related to it? I have to say, I am a bit lost.

Thanks for the help,

Martin

• ###### 1. Re: number of users between interval

Hi Martin,

You can start with creating bins based on start date and end date and after that you can create histogram based on bin.

Rahul

• ###### 2. Re: number of users between interval

I understand your point. But how is it possible to create bins from 2 dimensions?

Also, afterwards, will I have in those bins all the lines regrouped ( for example, by hour)?

Maritn

• ###### 3. Re: number of users between interval

Hi Martin,

you need to create a calculated field by using start and end date and then create bin on it.

It would be easier to help if you can share sample data.

• ###### 4. Re: number of users between interval

HI,

yeah it will be easier.

here is the link to a sample of my data: tableau Bins - Google Sheets

Thanks for your time.

• ###### 5. Re: number of users between interval

Hello Martin,

You can achieve this with help of parameters.

I assuming sample data as-

Then write calculations as to check count of distinct users in a day

Then take average of this count.

Find attached Twbx

• ###### 6. Re: number of users between interval

Martin -

Megha's answer should do it for you for a particular time. If you want to make a visualization of all times, then you will probably need to create a separate calendar table and join it to your original data. You can find several examples at FAQ:  Open & Close Dates

When you said you wanted to know how many people were in the shop at 8am, did you mean at the instant 8am, or did you mean any time between 8am and 8:59am? In other words, if a customer walks in at 8:47am, do they count for 8am or only 9am?

Thanks

Dan

• ###### 7. Re: number of users between interval

It looks like you created a calendar sheet with all the times of the day. That's a useful first step.

It takes some doing to get the right inequality as you need to compute everything in terms of time of day. I ended up with the following:

([Adjusted Date End]-DATETRUNC('day',[Adjusted Date End]))>=(FLOAT(DATEADD('day',2,[Bins start]))) AND ([Date Start]-DATETRUNC('day',[Date Start]))<=(FLOAT(DATEADD('day',2,[Bins end])))

You also have several records that show someone stayed present for multiple days. I don't know how you want to deal with those. I assume they are data errors of some kind. I ended up just cutting these off at the end of the day. But this has a big impact on the average number of people per hour, as now we have records all the way up to the end of the day. Here's the formula I used for that adjustment:

MIN(DATEADD('second',-1,DATEADD('day',1,DATETRUNC('day',[Date Start]))),[Date End])

It might make more sense to cut them off at 5PM or 6PM or something like that.

You need to count the number of days. This is a bit messy too. Did you want the average over all days in your data? Or only days when people are in your shop? Or only days when more than one person is in your shop? Or only when people are there for more than a few minutes? I ended up just counting days for which you have at least some data and that aren't weekends:

{FIXED: COUNTD(IF DATEPART('weekday',[Date Start])<>1 AND DATEPART('weekday',[Date Start])<>7 THEN DATETRUNC('day',[Date Start]) END)}

This counts 70 days. But there are still several which appear to be outliers (Oct 31, Nov 2, Nov 5, Nov 6, Dec 31, Feb 4, Feb 11)

I noticed a couple of cases where someone appeared to clock in, then immediately back out, then immediately back in again. Those users can get counted twice for that minute. So I used COUNTD([Firstname]) to count the unique people.

Then you just count records and divide. This says the maximum number is about 23.6 and near noon.

I also tried using the MEDIAN instead of the average (mean). This is less sensitive to outliers. The maximum is 26 from roughly 11:15 to 1:45. Note that this graph is based only on data where is at least one person in the shop at a particular moment.

I also made a viz just showing each of the separate days. A typical day tops out at 30 and the busiest days top out at 37.

Enjoy!

Dan

• ###### 8. Re: number of users between interval

Hi,

I mean the occupation between 8 an 9. I used the trunc functiun for that.

I actually did a intern join with another table so that for every hour I knew if the client was in ( true ) or false.

I then count every true value

Thank you all for your input.

Martin