Consulting

Results 1 to 7 of 7

Thread: With PivotTable: Count consecutive days that meet a criteria

  1. #1

    Exclamation With PivotTable: Count consecutive days that meet a criteria

    Hello, so I need to display a list of names that will meet 3 criteria: Absent for 2 days, 3 days, and over 3 days. And the days should be consecutive. Also I need to use a pivot table.

    I have never used a calculated pivot table. Please help. Attached a sample workbook.
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Is absent Friday and absent Monday two consecutive Absents?

    One might start by sorting the table by Status >> Name >> Date.

    One might also add Saturday and Sunday Dates with Status = NA or AO. This Status would also account for Authorized days Off.

    Unless this is a Homework assignment, which scenarios, are of course, not required to have a one to one relation with reality.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    At cell F7 in the attached, Power Query to Pivot table.
    Change the data in Table1 and right-click the pivot and choose Refresh.
    Not quite in the same format, for which you'd need to bring the data into the Data Model and use an additional measure in Power Pivot
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Quote Originally Posted by SamT View Post
    Is absent Friday and absent Monday two consecutive Absents?

    One might start by sorting the table by Status >> Name >> Date.

    One might also add Saturday and Sunday Dates with Status = NA or AO. This Status would also account for Authorized days Off.

    Unless this is a Homework assignment, which scenarios, are of course, not required to have a one to one relation with reality.

    Hello SamT,

    1. Is absent Friday and absent Monday two consecutive Absents?- Yes if their off is Saturday and Sunday
    2. Unless this is a Homework assignment, which scenarios, are of course, not required to have a one to one relation with reality. -It's for work

  5. #5
    Quote Originally Posted by p45cal View Post
    At cell F7 in the attached, Power Query to Pivot table.
    Change the data in Table1 and right-click the pivot and choose Refresh.
    Not quite in the same format, for which you'd need to bring the data into the Data Model and use an additional measure in Power Pivot

    What's Power Pivot
    Do I need to install anything else?

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by AaliiyahN82 View Post
    What's Power Pivot
    Do I need to install anything else?
    Depends on your version of Excel.
    If you change the data and refresh, does it work?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    1. Is absent Friday and absent Monday two consecutive Absents?- Yes if their off is Saturday and Sunday
    Well... there are two possibilities:
    1) No one ever works weekends or holidays,
    2) sometimes someone works weekend or holiday days.

    If 2), then Absent Friday and Monday, but worked Saturday does not fit the criteria
    How is the Code/Solution supposed to determine?

    These are called "Edge Cases." We can prepare for them now, or you will find them when someone complains to HR.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •