Histogram using times

[Anonymous]

Hello clever people!

Can you help?  I have a LONG list of people's movements (scan in / scan out), a sample shown below.  I'd like to form a Histogram, counting the culmulative number of people in and out within 15 minute windows.

For example.  If I started with 1000 people at 8am, what was my total at 8:15am, and at 8:30 mins (assuming more left or came back)...   the data below is an example of the format, and extend throughout a 24hr period.

Thanks everyone!

Date	Time	Scan
9/16/2016	12:59:47 PM	In
9/16/2016	12:59:42 PM	Out
9/16/2016	12:59:29 PM	in
9/16/2016	12:59:23 PM	in
9/16/2016	12:59:17 PM	in
9/16/2016	12:59:12 PM	in
9/16/2016	12:59:02 PM	in
9/16/2016	12:58:59 PM	out
9/16/2016	12:58:50 PM	out
9/16/2016	12:58:44 PM	out
9/16/2016	12:58:44 PM	In
9/16/2016	12:58:41 PM	Out
9/16/2016	12:58:33 PM	in
9/16/2016	12:58:30 PM	in
9/16/2016	12:58:29 PM	in
9/16/2016	12:58:14 PM	in
9/16/2016	12:55:00 PM	in
9/16/2016	12:54:52 PM	out
9/16/2016	12:54:51 PM	out
9/16/2016	12:54:49 PM	out
9/16/2016	12:54:45 PM	In
9/16/2016	12:54:38 PM	Out
9/16/2016	12:54:06 PM	in
9/16/2016	12:53:37 PM	in

[ankitpatira · Accepted solution]

@Anonymous so you create another calculated column with HOUR() function that will give you number in 24 HR format. Use that on x-axis to see peak and dips of people coming in and out during the day.

=HOUR(timeColumn)

[ankitpatira]

@Anonymous I think you just need to create calculated column as below and then use count of scan column as Values for column chart.

=IF( AND( MINUTE(TABLE[Time]) > 0, MINUTE(TABLE[Time]) <16), "0-15",
IF( AND(MINUTE(TABLE[Time]) > 15, MINUTE(TABLE[Time]) < 31), "15-30",
IF( AND(MINUTE(TABLE[Time]) > 30, MINUTE(TABLE[Time]) < 46), "30-45",
IF( AND(MINUTE(TABLE[Time]) > 45, MINUTE(TABLE[Time]) <= 59), "45-59", BLANK() ) ) ) )

[Anonymous]

Thanks @ankitpatira,

This works when all entering or leaving are within 1 hr (MINUTE >0 but <59).  Is there a way of doing this, if you're looking for the stats for people coming/going during a full day?

Think of it like an attendance tracker for people who are free to come and go, but we wish to see the peaks and dips through the day.

Thanks in advance!

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

To see the peaks and dips in one day about scan in and scan out, you can create a calculated column mentioned by @ankitpatira to calculate Hour=Hour([Time]). Then drag a line chart into the report, place "Hour" in Axis property, and "Scan" in Legend and Values (with count aggregate function). See the sample below:

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[ankitpatira · Accepted solution]

@Anonymous so you create another calculated column with HOUR() function that will give you number in 24 HR format. Use that on x-axis to see peak and dips of people coming in and out during the day.

=HOUR(timeColumn)