Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Histogram using times

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!

 

DateTimeScan
9/16/201612:59:47 PMIn
9/16/201612:59:42 PMOut
9/16/201612:59:29 PMin
9/16/201612:59:23 PMin
9/16/201612:59:17 PMin
9/16/201612:59:12 PMin
9/16/201612:59:02 PMin
9/16/201612:58:59 PMout
9/16/201612:58:50 PMout
9/16/201612:58:44 PMout
9/16/201612:58:44 PMIn
9/16/201612:58:41 PMOut
9/16/201612:58:33 PMin
9/16/201612:58:30 PMin
9/16/201612:58:29 PMin
9/16/201612:58:14 PMin
9/16/201612:55:00 PMin
9/16/201612:54:52 PMout
9/16/201612:54:51 PMout
9/16/201612:54:49 PMout
9/16/201612:54:45 PMIn
9/16/201612:54:38 PMOut
9/16/201612:54:06 PMin
9/16/201612:53:37 PMin
Need Help
  • ankitpatira's avatar
    ankitpatira
    9 years ago
    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)
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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

4 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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!

      • ankitpatira's avatar
        ankitpatira
        Community Champion
        9 years ago
        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)