Forum Discussion

jacosmit's avatar
jacosmit
Regular Visitor
7 years ago

Access control reports

Would dearly appreciate a set of fresh eyes. I've been tasked with building a report that provides some insights as based on data from an access control db (sample data attached) The idea is to prov...
Need Help
v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
7 years ago

Hi jacosmit ,

What is your desired reuslt for your formula? Could you please post your desired result if possible?

 

Regards,
Daniel He

  • jacosmit's avatar
    jacosmit
    Regular Visitor
    7 years ago

    Hi Daniel

     

    Something similar to the attached, per division.

    MONTHDIVISIONUSER_IDTIME ON SITERANKING
    Apr-19Division 01Joe Soap198:13:021
    Apr-19Division 01Frank Noble198:11:002
    Apr-19Division 01Stella Lange195:05:123
    Apr-19Division 01Frank Niemand185:00:234
    Apr-19Division 01Terry Crews156:00:155
    Apr-19Division 01Tom Cruise154:00:146
    Apr-19Division 01Rob Nelson150:10:157
    Apr-19Division 01Ted Mosley149:02:458
    Apr-19Division 01Sean Els148:00:159
    Apr-19Division 01Tom Brady147:15:3210
    • v-danhe-msft's avatar
      v-danhe-msft
      Microsoft Employee
      7 years ago

      Hi jacosmit ,

      So could you mean the TIME ON SITE and RANKING column is your desired column? How the logic about the TIME ON SITE column?

       

      Regards,

      Daniel He

       

      • jacosmit's avatar
        jacosmit
        Regular Visitor
        7 years ago

        Hi Daniel

         

        Yes, TIME ON SITE and RANKING would be what is required as output.

        Logic with regards to the TIME ON SITE...... Conceptually I wanted to work out the earliest and latest time per person per day. Then subtract latest with earliest to get to the amount of time spent on site per day. Then add all the daily totals to get to the monthly total.