Level 1 Model Building Exam - Model Building Activity 6

NoahBG
New Contributor

Level 1 Model Building Exam - Model Building Activity 6

Hello all,

I have been trying to work through Model Building Activity level 6 and cannot seem to understand why my lookup formula is off. I am trying to add a formula to REP04 that shows Total Salary expenses by Region and Role. The formula I am using is 'EMP02 Employee Expenses'.Salary[Lookup: 'SYS08 Employee Details'.Region, Lookup:'SYS08 Employee Details'.Role] but it receives an error. 

 

How I understand it is that 'REP04 Employee Expense Summary'.Salary is my target and 'EMP02 Employee Espenses'.Salary' is my source module. Because EMP02 does not contain Region and Role I need a mapping module/s to achieve my goal. I am trying to use 'SYS08 Employee Details'.Region and 'SYS08 Employee Details'.Role for mapping but receive an error. What am I missing? Do I need to be using a different module? If more details are needed please let me know. Thanks for any help!

 

Error message: Format of mapping used for lookup doesn't match any dimension of the source

 

Helpful reference post I believe is relevant: https://community.anaplan.com/t5/Academy-Training/Altering-Line-Item-Formula-with-lookup/m-p/88856#M...

 

Screenshots:

NoahBG_0-1626889409586.png

 

NoahBG_1-1626889438245.png

 

NoahBG_2-1626889453860.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
JaredDolich
Moderator

@NoahBG 

Oh, so, so close. You used LOOKUP. Try using SUM instead. Your logic is flawless. Just the wrong function. You got this!

'EMP02 Employee Expenses'.Salary[SUM: 'SYS08 Employee Details'.Region, SUM: 'SYS08 Employee Details'.Role]


Jared Dolich

View solution in original post

8 REPLIES 8
JaredDolich
Moderator

@NoahBG 

Oh, so, so close. You used LOOKUP. Try using SUM instead. Your logic is flawless. Just the wrong function. You got this!

'EMP02 Employee Expenses'.Salary[SUM: 'SYS08 Employee Details'.Region, SUM: 'SYS08 Employee Details'.Role]


Jared Dolich
NoahBG
New Contributor

@JaredDolich 

Thank you so much! I tried using SUM twice but must have had some kind of typo. It is working now. I appreciate the help and encouragement! 

 

For those referencing the post in the future. My end working formula is below:

 

'EMP02 Employee Expenses'.Salary[SUM: 'SYS08 Employee Details'.Region, SUM: 'SYS08 Employee Details'.Role]

aykcos
Contributor

Hi,

 

I have set up formula for Salary without having a problem. But I am facing with the problem with finding correct formula for other line items in this model like "Bonus", "Car Costs", Phone Costs" and "Medical Benefit".

 

Lookup function does not work in this module because of dimensions mismatch. But SUM function does not work also.

 

Any hint for this please?

Bin.Zhou
New Contributor

have you got answer for the formula to sum up Bonus? I run into the same issue as you have.
aykcos
Contributor

I solved this problem by considering the differency between dimensions. You should pull data from another module and convert it by correct mapping considering the dimensions. 

 

Target module's dimensions:

E2 Employees#: Active?

Time

Line Items

 

Source module's dimensions:

E1 Departments

Role

Line Items

 

So, you should take the first 2 dimensions of source module into account for mapping.

I hope this helps. 

kunle
Contributor

I am struggling with Activity 6 despite following some of the suggestions on the thread. Please can someone suggest a hint as to a way out.

Here is the error i am seeing when i create my formula:

Error.PNG

 

Below is the Blueprint view on my REP04, EMP02 and SYS08

REP04 Blueprint view.PNG

EMP02 Blueprint view.PNG

SYS08 Blueprint view.PNG

 

Thank you.

egunerhan
New Contributor

Role item probably not listed correctly 

To solve this problem 

1. Go to SUM: SYS08 Employee Details

2. Look at the format of "Role "

3.Make the changes as below 

Format-Role

Type: List

List: Role

 

Good luck to whoever may need this explanation!

nileshs
New Contributor

Hi,

When I use my SUM formula I am getting blank values.

 

Also I do not see any data in sys08.region and sys08.role. its blank. how to get the data here. I have formatted them as list G1 region and E3 role.