Level 1 Exam - Question 5

TLam99
Occasional Contributor

Level 1 Exam - Question 5

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 

1 ACCEPTED SOLUTION

Accepted Solutions
gaurav_sharma-bng
Certified Master Anaplanner

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

 

Regards,

Gaurav

View solution in original post

10 REPLIES 10
anand.shekhawat
Valued Contributor

@TLam99 

 

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

anandshekhawat_0-1649941845279.png

 

~Anand

 

cuneydyasin
Contributor

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
Contributor

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
Contributor

 

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
Certified Master Anaplanner

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
Contributor

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
Certified Master Anaplanner

@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
Contributor

Wow that worked. Thanks!

cuneydyasin
Contributor

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.