Level 1 Exam - Question 5

TLam99
TLam99
in Academy and learning discussions

Need to update the formula in EMP02 with EMP03's info. But I keep getting an error and I have no idea where I have gone wrong... Can anyone point me at the right direction?

 

This is the formula I used...

IF Employed? THEN Salary *'EMP03 Employee Drivers by Role'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0

 

But getting this error?

Invalid formula for 'EMP02 Employee Expenses'.Bonus: Automatic sum of 'EMP03 Employee Drivers by Role'.Bonus % over E3 Role hierarchy is not possible as E3 Role does not have a built-in top level.

 

I have been on it for hours! Any help is appreciated

 

Thanks

Tony 

Tagged:

Comments

  • anand.shekhawat
    anand.shekhawat

    @TLam99 

     

    Possibly the top level is missing in your 'Roles' List, you need to create the same.

    anandshekhawat_0-1649941845279.png

     

    ~Anand

     

  • cuneydyasin
    cuneydyasin

    This doesn't work unfortunately.

    cuneydyasin_0-1654058941966.png

    Plus "All Roles" is already at the top level in this list:

    cuneydyasin_1-1654058994441.png

     

  • cuneydyasin
    cuneydyasin

    I solved this by deleting "All Roles" and inserting this line back as the parent.

    The problem continues though. This time it says:

    cuneydyasin_0-1654062547330.png

     

  • cuneydyasin
    cuneydyasin

     

    Now I'm trying to change the format of Roles in SYS08, from text to list. But it gives this error:

     

    Format of mapping used for lookup doesn't match any dimension of the source.

  • gaurav_sharma-bng
    gaurav_sharma-bng

    Hi @TLam99 and @cuneydyasin ,

     

    I guess this could be a dimensionality issue. Let me share screenshots of a working formula and please check the dimensions in your model and the structure. You don't need 'All Role' here because you are searching for a Bonus percentage for each employee based on the department and the role of that employee.

     

    SYS08 module - Dimensioned by Employee

    gaurav_sharmabng_0-1654067387879.png

     

    EMP03 - Dimensioned by Role and Department and has Bonus% as line item

    gaurav_sharmabng_1-1654067449175.png

    EMP02 - Uses the shown formula to fetch the Bonus% and multiply with existing salary of employee

    gaurav_sharmabng_2-1654067505982.png

     

    Let me know in case of any concerns.

     

    Regards,

    Gaurav

     

     

  • cuneydyasin
    cuneydyasin

    Thanks for the response..

    We need "All Roles" not because its calculations will be used in any cell, but because the list could not be referred by the formula in the case of a non-existent parent.

    It seems that your "Role" column in SYS08 is formatted as a list, since you can choose a value from a dropdown menu. This is the issue for me actually: I can't change it from text to list (by referring to the Roles list). When I try, it gives this error: "Format of mapping used for lookup doesn't match any dimension of the source."

  • gaurav_sharma-bng
    gaurav_sharma-bng

    @cuneydyasin - Can you check the reference of the 'Role' line item in SYS08 module. It must be getting referred to calculated another line item in a module. You would first need to remove that formula to make the 'Referenced by' blank and then change the line item format to 'Role' list

     

    gaurav_sharmabng_0-1654070339214.png

     

  • cuneydyasin
    cuneydyasin

    Wow that worked. Thanks!

  • cuneydyasin
    cuneydyasin

    I just wanted to write this as well:

    My solution was creating a Role2 column and re-importing the data for only this column and formatting it as a list. I would refer to this column in EMP02 formulas.

  • gaurav_sharma-bng
    gaurav_sharma-bng

    @cuneydyasin - pls mark it as a solution so that others can refer to it as well.

     

    Regards,

    Gaurav

Categories