locked
Countrows filtering RRS feed

  • Question

  • The idea of this is to calculated overnights stays done by employee when they are on service trips. Here are the column I've got


    I've highlighted the tasks that need to be counted for a total of 9 overnight stays. As you can see I need to filter out the travel time tasks on the days the employee is heading back along with other tasks done on that day (In Plant Time on 6/7/2018 for example.

    My questions are how can I account for employees that do more than one trip such as this along with also removing tasks done on the day the employee is heading home.

    I was trying to do it by just removing the max timesheetDate when the task is travel date, but in this case that doesn't filter out enough.

    Thanks in advance.

    Monday, July 30, 2018 6:50 PM

All replies

  • Hi jshinnenkamp,

    Thanks for your question.

    >>>My questions are how can I account for employees that do more than one trip such as this along with also removing tasks done on the day the employee is heading home.
    The sample data provided by you do not contain information about employees and overnight stays. With this limited information, It is pretty hard to answer your question. To solve your question more efficiently, please share all the information that needed to solve this question. Thanks for your support.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 31, 2018 2:16 AM
  • Sorry for the confusion, Ill try to explain it better. I'm trying to get a total number of nights an employee spent overnight at a plant.

    Going row by row in the image above. The first row (6/4/2018) is when the employee left for their service trip noted as "Travel Time". They then spent 4 nights at the plant (6/4, 6/5, and 6/6). The next day they traveled back home so the tasks on 6/7/2018 are not part of overnight stays. Then on 6/19 they left for another trip and spent those three nights there before traveling back on 6/22. The third trip began on 6/25 and again spent three nights before returning on 6/28. 

    So basically I need to filter out all of the tasks on the days the employee traveled back and did not stay overnight. (The highlighted rows.)

    Tuesday, July 31, 2018 1:50 PM
  • The source data is hardly adequate for this analysis. What you need is more detailed information on travel, where travel to plant can be differentiated from travel home. Once you get that, you just count distinct dates on which there were no travel home events.

    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, July 31, 2018 2:06 PM
  • Would there be a way to use the date column to decipher when trips start and end? By noticing when there are gaps in days (like from 6/7 to 6/19). I'm not sure how to see if the next date in a column is the next day or if there is a gap. 

    Or maybe a way to mark alternative "Travel Time" tasks as to and from.

    Within the table these seems to be the only relevant columns, so maybe this is just a unrealistic task without more information given.

    Tuesday, July 31, 2018 8:03 PM
  • It might be possible to infer which travel is 'to' and which is 'from' just from these three columns. You'll need the logs to be complete with no records missing, and also you'll need rows sorted in the correct order (note the two events on 6/7 and imagine them swapped). Your sample complies with these requirements, but this is no guarantee that the whole dataset will...

    Expect me to help you solve your problems, not to solve your problems for you.

    • Proposed as answer by alexander fun Wednesday, August 1, 2018 11:04 AM
    Tuesday, July 31, 2018 8:53 PM