Level 1 Modeling Activity #5

RRawmplano
RRawmplano
edited March 2023 in Academy Discussions

Hello - I have been having similar issues with the formula updates to the Bonus%, Car Costs, Phone Costs and Medical Costs in the EMP02 Module - when I try to update the formula as below I receive the error message.  I am seeing posts about removing the Role List and reimporting without the Parent Column and the reimporting into the EMP03 module - is this correct?

 

Error message - Invalid formula for 'EMP02 Employee Expenses'.Car Costs: Automatic sum of 'EMP03 Employee Drivers by Role'.Car Allowance over Role hierarchy is not possible as Role does not have a built-in top level

 

When I resolve this is this formula (below) correct?

IF Employed? THEN 'EMP03 Employee Drivers by Role'.Car Allowance[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0

 

Any assistance will be appreciated ..Thank you.

 

 

Tagged:

Answers

  • bhatjaved
    bhatjaved

    @RRawmplano have you built up top level to role list ? if not please add top level as All roles than your formula should work .

  • egomez66
    egomez66

    Here are few things to check:
    1) You have created the Role List
    2) You imported the Role List
    3) You created a line item in the SYS08 as Role. This should be formatted as List and reference to the Role List
    4)You imported the SYS08 and it should update the line item Role and you should be able to see the roles filled for all the employees

    Now if you have done correctly above, then you should not have an error

  • RRawmplano
    RRawmplano

    Thank you - changing the format to List Role seemed to have done it - appreciated..

  • MPWall
    MPWall
    edited March 2023

    Can I ask regarding point 4, importing SYS08 into Roles or Roles into SYS08? Or are you importing Employee Drivers again and having it find the Roles this time? I am stuck on this issue and most of the data on this issue seems to be from over a year ago and not helping.

  • AjayM
    AjayM

    Hi @MPWall ,

    As Edgar (egomez66) mentioned in the list, if you have completed first 3 points, the fourth one talks about reimporting 'employee details by role' file into SYS08 module. After this, you should find ‘Role’ lineitem in SYS08 populated with roles data.

    Hope this helps.

    Cheers!

  • MPWall
    MPWall

    Role in Other Lists, Top level is All Roles, Line Item in SYS 08 shows roles of each person, I still get mismatch error, do I have to change EMP02 to yearly for date? or is there something I should be looking for in the formating of sys08, emp03, emp02 that would allow me to get the correct values instead of 0's?

    Thanks

    MPW

  • MPWall
    MPWall
    edited March 2023

    Could it be how I have EMP03 laid out?

    EMP03 Pivot.png
    EMP03 Pivot.png

  • AjayM
    AjayM

    Hi @MPWall ,

    Your EMP03 looks fine, and EMP02 is supposed to be at month level. If you can please share blueprints of SYS08 and EMP02, along with the formula that you are trying for any of the costs, that would be helpful to find the issue.

  • MPWall
    MPWall
    EMP02 bp.png
    sys08 bp.png
    EMP02 formula.png

    As I understand it, I am trying to change the source in the current EMP02 bonus line from EMP01 to EMP03 with a look up in SYS08 for Dept and Role. I am getting line item err and non matching err.

    EMP02 err.png
    Err Formula Bonus by Role.png

    Thanks

  • AjayM
    AjayM

    You are right about switching the source, but you are doing cost related calculations in SYS08 module, which is not right. As you rightly said, you have to change the formula in EMP02 by changing the source references to EMP03 instead of EMP01.

    Ex: Bonus in EMP02 = Monthly Salary * Bonus% in EMP03 (use lookup on department and role from SYS08)

  • MPWall
    MPWall

    Got it, combination of things, but finally penny dropped and put it all together and got the correct answers. On to the next one.

    MPW

Categories