How many people used their entire sick leave entitlement? [Power Query / Excel homework]

Excel Challenges - 17 comments

I have a big day tomorrow. We are celebrating our kids (Nishanth & Nakshatra) 9th birthday this weekend. We (Jo & I) must have been possessed by demons, for we are hosting the party at our home. 12 kids (half of each kind) will be invading our house for a couple of hours. There will be balloons to blow, cakes to bake, decorations to dangle, meals to make, children to cheer, guests to greet, dishes to wash, carpets to clean, walls to varnish, furniture to fix and Chandoo to calm.

So let’s keep this quick and simple. I want you to figure out an elegant and simple way to answer below questions.

Sick leave entitlement vs usage - Excel problem

Imagine you are the HR analyst at BigLargeCompany. You need to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.

You have two tables – emps & leaves as illustrated below.

sample data - sick leave entitlement vs. usage

Your mission is to find out answers to below questions.

  • How many employees used exactly 100% of their entitled sick leave?
  • How many employees did not take any sick leaves?
  • Listing of all employees who used 100% of their entitlement

 

Use either Power Query, Excel formulas or any other technique to answer the questions.

 

Please download sample data for this exercise here.

 

Once you have answers, post them in comments section.

 

Want more problems? Check out Excel homework section for some very tricky, interesting challenges.

Talk to you soon. If you need me, I will be scraping ketch up off carpets.

Update: Solution is here

If you want to learn how to solve problems like this, check out the Entitlement vs. usage analysis – solution page.

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

17 Responses to “How many people used their entire sick leave entitlement? [Power Query / Excel homework]”

  1. Chihiro says:

    PQ - On leaves, Group by [Emp ID] and aggregate [Days used] by sum.
    Left Join to emps.
    Add custom column.
    = try [Days used]/[Entitlement] otherwise 0

    Then use pivot table(s) to summarize (using slicers). Alternately use custom functions to aggregate data to return single value (Table.SelectRows, Table.RowCount).

    If you have PowerPivot (DAX) available. There are number of ways to set up model for this data. 1 & 2 are preferable over 3.
    1. Create single flat table as above. Use DAX measures and cube functions to summarize.
    2. Create 2 separate dimension from emps, 1 for unlimited entitlement and another for limited entitlement. Report using multiple visual/pivottable.
    3. Load both to model after Group by operation. Add calculated columns to leave table to handle unlimited entitlement.

    • Chihiro says:

      Here's DAX solutions using model based on option 2.

      No absence:
      No Abs:=COUNTROWS(Unlimited)+COUNTROWS(Limited)-COUNTROWS('leaves')

      Exactly 100%:
      Count Exact:=COUNTX(FILTER('leaves',RELATED(Limited[Emp ID])BLANK()),IF([Days used]/RELATED(Limited[Entitlement])=1,1,Blank()))

      100% & Over Count:
      Count 100% and Over:=COUNTX(FILTER('leaves',RELATED(Limited[Emp ID])BLANK()),IF([Days used]/RELATED(Limited[Entitlement])>=1,1,Blank()))

      For the list, used Pivot Table (you can use CONCATENATEX... but that'd make it hard to read).

  2. Tim Weldon says:

    21, 10, 67

  3. How many employees used exactly 100% of their entitled sick leave? = 21.
    How many employees did not take any sick leaves? = 10.
    Listing of all employees who used 100% of their entitlement = EMP IDs - 35, 77, 78, 273, 276, 281, 301, 316, 338 & 416.

  4. Fowmy says:

    I used PQ to solve the problem

    File link: https://1drv.ms/x/s!AmoScH5srsIYvRntB_Cm_U53nFh2

    1. Leave used exactly 100% : 21
    2. Didn't take any leave : 10
    3. Used 100% of the entitlement : 67

    List (5,7,12,30,32,34,43,50,61,79,89,90,91,93,101,103,104,105,119,128,131,139,141,150,157,170,175,176,177,178,179,186,188,191,215,225,231,238,240,249,252,257,259,278,288,295,298,306,312,313,317,318,323,324,327,334,342,344,345,346,370,379,381,387,392,401,415)

  5. David N says:

    Exactly 100% = 21
    =SUMPRODUCT(--(SUMIFS(leaves[Days used],leaves[Emp ID],emps[Emp ID])=emps[Entitlement]))

    At Least 100% = 67
    =SUMPRODUCT(--(SUMIFS(leaves[Days used],leaves[Emp ID],emps[Emp ID])>=emps[Entitlement]))

    None = 10
    =SUMPRODUCT(--(SUMIFS(leaves[Days used],leaves[Emp ID],emps[Emp ID])=0))

    Getting the list required something more than regular functions. I used a personal VBA function that returns a delimited list of multiple matches and chose to obtain those IDs for the Exactly 100% case.
    7; 12; 91; 103; 104; 131; 157; 170; 176; 177; 225; 249; 295; 298; 313; 317; 342; 346; 381; 392; 415

  6. Create a query for each table ... I chose Close and Load to the same worksheet in each case
    Now Append leaves query to emps query to give me a three column query: Emp ID, Entitlement, Spent
    Use this new query to create a Pivot Table:
    Emp ID in Rows
    For demonstration I put Entitlement and Spent in the Values area ... noting that Entitlement will show as Count because there are text entries in that column so you have to make it show Sum
    I now created a Calculated Field called Entitl less Spent to show the Balance on the Leave Account for each employee
    From that column, my answers:
    21 people used all of their entitlement
    Emloyees who took no leave:
    35
    77
    78
    273
    276
    281
    301
    316
    338
    416
    The 21 people who have taken all of their entitlement are:
    7
    12
    91
    103
    104
    131
    157
    170
    176
    177
    225
    249
    295
    298
    313
    317
    342
    346
    381
    392
    415

    If I am wrong here, please point out my errors, I would be grateful!

  7. Robert H. Gascon says:

    1. Employees who used 100% of their entitlement = 67;
    =SUMPRODUCT(--(SUMIF(leaves[Emp ID],
    emps[Emp ID],
    leaves[Days used])>=emps[Entitlement]))

    2. Employees who took no sick leave = 10;
    =SUMPRODUCT(--(SUMIF(leaves[Emp ID],
    emps[Emp ID],
    leaves[Days used])=0))

    3. List of 67 employees in Item 1, starting with cell O7 downwards;
    =IFERROR(LOOKUP(1,
    1/(1/ROW(emps[Emp ID])=MAX(INDEX(1/ROW(emps[Emp ID])*(SUMIF(leaves[Emp ID],emps[Emp ID],leaves[Days used])>=emps[Entitlement])*(COUNTIF(O$6:O6,emps[Emp ID])=0),0))),
    emps[Emp ID]),"")

    Note: 1/ROW(emps[Emp ID]) is the imaginary helper column and (COUNTIF(O$6:O6,emps[Emp ID])=0) filters for employees not yet listed.

  8. GraH says:

    Load both tables as connection only

    1) emp
    let
    Source = Excel.CurrentWorkbook(){[Name="emps"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", type text}}),
    FilterOutX = Table.SelectRows(#"Changed Type", each ([Entitlement] "x")),
    ChangeToNumber = Table.TransformColumnTypes(FilterOutX,{{"Entitlement", Int64.Type}})
    in
    ChangeToNumber

    2) leaves, already grouped to sum the days used
    let
    Source = Excel.CurrentWorkbook(){[Name="leaves"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Days used", Int64.Type}, {"Emp ID", type text}}),
    SumOfDaysUsed = Table.Group(#"Changed Type", {"Emp ID"}, {{"Days Used", each List.Sum([Days used]), type number}}),
    SetAsNumber = Table.TransformColumnTypes(SumOfDaysUsed,{{"Days Used", Int64.Type}})
    in
    SetAsNumber

    3) Analysis: I appended both tables, sorted, grouped to sum, calculated remaining, defined categories, filter the categories and extracted the list of ID's.

    let
    Source = Table.Combine({emps, leaves}),
    SortOnID = Table.Sort(Source,{{"Emp ID", Order.Ascending}}),
    SumValues = Table.Group(SortOnID, {"Emp ID"}, {{"Entitlement", each List.Sum([Entitlement]), type anynonnull}, {"Days Used", each List.Sum([Days Used]), type number}}),
    FilterOutUnlimted = Table.SelectRows(SumValues, each ([Entitlement] null)),
    CalcRemaining = Table.AddColumn(FilterOutUnlimted, "Remaining", each if [Entitlement]=null then null else [Entitlement]-[Days Used]),
    DefineCategory = Table.AddColumn(CalcRemaining, "Category", each if [Remaining] = 0 then "All sick days taken" else if [Days Used] = null then "No sick days taken" else if [Remaining] < 0 then "More sick days taken then entitled" else "Some days taken"),
    FilterOnScope = Table.SelectRows(DefineCategory, each ([Category] = "All sick days taken" or [Category] = "No sick days taken")),
    GroupCategory = Table.Group(FilterOnScope, {"Category"}, {{"AsTable", each _, type table}, {"Count Employees", each Table.RowCount(_), type number}}),
    ListIDs = Table.AddColumn(GroupCategory, "List IDs", each if [Category] "All sick days taken" then null else List.Accumulate(Table.Column([AsTable],"Emp ID") ,"", (state,current) => if state = "" then current else state & ", " & current ))
    in
    ListIDs

    Analysis loaded to workbook:
    - 21 employees took exactly 100% of entitled sickness.
    - List is {7, 12, 91, 103, 104, 131, 157, 170, 176, 177, 225, 249, 295, 298, 313, 317, 342, 346, 381, 392, 415}
    - 10 have taken no sick leave

    • GraH says:

      Okay, I misread the assignment

      My corrected analysis query
      let
      Source = Table.Combine({emps, leaves}),
      SortOnID = Table.Sort(Source,{{"Emp ID", Order.Ascending}}),
      SumValues = Table.Group(SortOnID, {"Emp ID"}, {{"Entitlement", each List.Sum([Entitlement]), type anynonnull}, {"Days Used", each List.Sum([Days Used]), type number}}),
      FilterOutUnlimted = Table.SelectRows(SumValues, each ([Entitlement] null)),
      CalcRemaining = Table.AddColumn(FilterOutUnlimted, "Remaining", each if [Entitlement]=null then null else [Entitlement]-[Days Used]),
      DefineCategory = Table.AddColumn(CalcRemaining, "Category", each if [Remaining] = 0 then "Exactly all sick days taken" else if [Days Used] = null then "No sick days taken" else if [Remaining] < 0 then "More sick days taken then entitled" else "Some days taken"),
      FilterOnScope = Table.SelectRows(DefineCategory, each ([Category] "Some days taken")),
      AddCat2 = Table.AddColumn(FilterOnScope, "Category2", each if [Category] = "No sick days taken" then [Category] else "All entitled sick days taken"),
      GroupCategory = Table.Group(AddCat2, {"Category2", "Category"}, {{"AsTable", each _, type table}, {"Count Employees", each Table.RowCount(_), type number}}),
      ListIDs = Table.AddColumn(GroupCategory, "List IDs", each if [Category2] "All entitled sick days taken" then null else if [Category] = "Exactly all sick days taken" then null else List.Accumulate(Table.Column([AsTable],"Emp ID") ,"", (state,current) => if state = "" then current else state & ", " & current )),
      AddGroupCat = Table.AddColumn(ListIDs, "Group", each if [Category] = "Exactly all sick days taken" then [Category] else [Category2]),
      RemoveCol = Table.RemoveColumns(AddGroupCat,{"Category2", "Category", "AsTable"}),
      GroupAsFirstCol = Table.ReorderColumns(RemoveCol,{"Group", "Count Employees", "List IDs"})
      in
      GroupAsFirstCol

  9. Tim says:

    It took me about ten minutes to sort, sum and count. How long does Power Query take?

    • Terry W says:

      Hi Tim,

      It took roughly 6~8 minutes to use PQ to work out the answers.

      I am new to PQ but I think what is more valuable is that PQ allow you to simply replace the source data to get the result in one click (refresh data). So you do not need to spend another 10 minute next time for a different set of data.

      Cheers,

      Terry

  10. Terry W says:

    1) Import both tables (Emps and Leaves) to PQ;
    2) Group 'Days Used' column in the Leaves table by Emp ID, and then merge the new table with Emps table, then expand the new merged column to show Entitlement;
    3) Duplicate the new Leaves table, add a new column to calculate the difference between the 'Total Days Used' column and 'Entitlement' column, then filter the difference column by '0'. Remove all columns except the Emp ID column and then use the Count Rows function to find out the total number of employees who used exactly 100% of their leaves;
    4) Merge the Emps table with Leaves table, expand the new merged column to show Total Days Left. Filter the column by null, Remove all columns except the Emp ID column and then use the Count Rows function to find out the total number of employees who used exactly 100% of their leaves;
    5) Duplicate the new table from Step 3, alter the filter step to show all employees who have used 100% of their entitlement or more. Remove the step of Count Rows and you will have the list of employees who have no entitlement left.

    The answers are 21, 10, and 67 (the answer for Q3 should be a list of employees but for simplicity I just use the total count as the answer)

  11. Romulo Escobar says:

    I used the following formulas:

    SUM(IF(SUMIFI(leaves[Emp ID],emps[Emp ID],leaves[Days used])=emps[Entitlement],1,0))

    SUM(IF(SUMIF(leaves[Emp ID],emps[Emp ID],leaves[Days used])=0,1,0))

    TEXTJOIN(", ",1,IF(IF(SUMIF(leaves[Emp ID],emps[Emp ID],leaves[Days used])=emps[Entitlement],1,"")=1,emps[Emp ID],""))

    the results were
    21
    10
    7, 12, 91, 103, 104, 131, 157, 170, 176, 177, 225, 249, 295, 298, 313, 317, 342, 346, 381, 392, 415

  12. Shobi Imran says:

    Great Article Chandoo, Thanks for sharing, Keep up the good work

  13. Peter Bartholomew says:

    Using named formulas. The first step was to sum the number of days leave for each employer, 'used':
    = SUMIFS(leaves[Days used],leaves[Emp ID], ID)
    where ID is a shorter name to refer to '=emps[Emp ID]'.

    To avoid the nuisance of CSE or SUMPRODUCT without the product, a couple of further named formulas 'zeroSickLeave?' and 'excessSickLeave?' to act as flags:
    = N(used=0)
    = N(emps[Entitlement] <= used)

    That leaves worksheet formulae
    = SUM( excessSickLeave? )
    = SUM( zeroSickLeave? )
    giving 67 and 10 respectively.

    The list requires the SMALL function
    = IFERROR( SMALL( IF(excessSickLeave?, ID ), ID ), "" )

    Roll on dynamic arrays and FILTER!

Leave a Reply


« »