Auto Allocate List Item to Line Item based on Boolean

MarkTurkenburg_*8288
MarkTurkenburg_*8288
in Modeling

Hi Team,

 

I'm trying to allocate unused #Job Roles to an Employee list through a Boolean identifier but cannot figure out what method I should use

 

The scenario is:

 

- I have a list of #Job Roles & a boolean line item which indicates which list items have not been allocated to employees (Module EM43)

- I have a list of Employees & a line item (formatted as #Job Roles) (Module EM42)

- I cannot figure out which formula to use so that 'Employee 1 - #337' would be allocated #Job Role #4 (The 1st Not Allocated), Employee 2 - #338 allocated #Job Role #7 etc

 

Any help would be most appreciated!

 

Mark

 

clipboard_image_0.png

clipboard_image_1.png

Tagged:

Answers

  • nathan_rudman
    nathan_rudman

    you need to "extract" the un-allocated role, like this:

    clipboard_image_0.png

    The summary type of First UnAllocated must be "First non blank". As you can see, the total "All Roles" becomes equal to the first role that is not used as per boolean. That means any module without the role dimension pulling this line item will resolve on the top level "All Roles"

     

    Then in your employee list module, you can simply do:

    IF ISblank(Job Role) THEN 'Job Role:extract unallocated'.First UnAllocated else Job Role

     

    Obviously that works only for one default un-allocated role. You could add a dimension to the "First UnAllocated" line item if you want to assign several un-allocated roles depending on country or department for example.

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    Hi Nathan,

     

    Thanks, that is a fantastic solution for the 'First Unallocated Job Role' line item but unfortunately I need to then allocate each of those roles to Employees but currently it is only allocating the 1st unallocated role across everyone

     

    The end aim was to have #Job Role #4 allocated to Employee 1, #Job Role #7 allocated to employee 2 and so forth

     

    You stated below that I would need to add a dimension but would that not double up the roles - e.g. Job #4 could go against Finance & Sales?

     

    What I am trying to do may not be possible & could require a manual workaround?

     

     

    clipboard_image_2.png

    clipboard_image_3.png

     

  • nathan_rudman
    nathan_rudman

    yeah I missed that part 🙂

     

    I have a question: what's the logic here ? I suppose you're going to have many more employees without a role than role un assigned, how is it going to be when all roles have been assigned once ? cycle back ? And the order at which these roles are assigned depend on what ? the order of the employees in the list ?

     

    Anyway, you can use the rank/rankcumulate formulas to count the employee without a job role, create a "placeholder" list with as many elements as employees without a role and apply that list to the logic I showed you. you can then add a [LOOKUP:rank] to find the right role. I can do that in a module once you answer my questions above 

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    All good

     

    The logic is:

     

    - At the beginning of a budget period we have a listing of current employees & forecast to be hired employees

    - We want all employees to be assigned a #role because in effect we are budgeting for a role rather than an employee. Therefore in the future if an employee leaves & is replaced etc we can view the actual v budget by #role rather than employee & mimimise the number of list items to analyse 

    - This #role will also roll up into a #group listing (for situations where 1 role may replace 2 budgeted roles) but this is out of the scope of this discussion

     

    We would like to assign the #role at the beginning of the budget period in an automatic fashion where:

    - All employees that have a categorisation of New/Existing role will be allocated a #role from the list (the list item itself is not important as it is purely used to identify the role)

    - Replacement role employees will take the role of their predecessor 

    - Any future employees added (New/Existing only) would be allocated a unused #role from the list

     

    All these roles could be reset at the beginning of the next budget period as they are only relevant for 1 year

     

    I hope that provides enough colour on what we are trying to achieve

     

    I appreciate your advice & guidance!

     

    Mark

     

     

  • amit.paul1
    amit.paul1

    Hi Mark,

    As suggested by Nathan this could be achieved using RANK. I am outlining the broad steps below:

    1. In EM43, create line item "Rank - Not allocated roles" to rank the #Job Roles items not allocated. Tip: Use the "Not Allocated?" boolean as "Include" parameter in the RANK formula. Line item: Rank - Not allocated roles, Format: Number, Applies to: #Job Roles
    2. Create another line item in EM 43, Line item: Not allocated roles by Rank, Format: #Job Roles, Applies to: Data List (1-100). Data list is a non production list containing numbers 1 - 100 (as many required). The idea is to transform the line item in Step 1 to a line item containing the roles dimension-ed by (1...100). i.e. Item 1 will contain first not allocated role, Item 2 will contain second not allocated role. Step 1 -> Step 2 can be easily achived using a formula (FIRSTNONBLANK)
    3. In EM42, create line item "Rank - Not allocated employees" to RANK the Employees items not allocated. Use logic similar to Step 1.
    4. In EM42, create Data List formatted line item "Rank - Not allocated employees List" based on "Rank - Not allocated employees" using FINDITEM on Data List
    5. In EM42, LOOKUP the role from line item in Step 2 using line item in Step 4. e.g.  EM43. Not allocated roles by Rank [ LOOKUP: EM42. Rank - Not allocated employees List]

    Hope that helps.

     

    Regards,

    Amit

Categories