Level 1 Exam - Question 5

TLam99

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 

anand.shekhawat

@TLam99 

 

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

 

~Anand

 

cuneydyasin

This doesn't work unfortunately.

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

 

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

 

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_17

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

 

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

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

 

Let me know in case of any concerns.

 

Regards,

Gaurav

 

 

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_17

@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

 

 

cuneydyasin

Wow that worked. Thanks!

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_17

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

 

Regards,

Gaurav