Level 1 Model : Dimension of mapping used for lookup doesn't match any dimension of the result.
Hi,
While adding formulas to the line items in EMP02 Employee Expenses we were asked to enter the below mentioned formula
| Bonus | IF Employed? THEN Salary * 'EMP01 Employee Drivers'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Country] ELSE 0 |
but it's throwing an error "Dimension of mapping used for lookup doesn't match any dimension of the result."
Please suggest to resolve this issue.
Answers
-
It is a clear mapping issue. I don't think you should multiply it with the Bonus % of Emp 01 Module instead bring it from Emp 03 Module. You can verify that by checking your dimensions of the the two modules. Your formula should be like
IF Employed? Then Salary * 'EMP 03 Employee Drivers by Role'.Bonus%[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0
Hope that helps
Misbah
0 -
Hi @Misbah,
Thank you for your prompt response.
We don't have EMP03 Module 'Employee Drivers by Role' in our Model. I tried this mentioned formula and that's why received an error for EMP03 "not a recognized line Item or list member".
Please suggest. Thanks in advance.
0 -
My bad. I think I jumped the gun. You will have to go step by step
Dimensions of Emp 01 module - Country and Department .
Dimensions of Emp 02 module - Only Employee (It is a Child of Department, Use lookup to bring values from Department to Employees You also have Country as a property of Employee(Another lookup)
Dimensions of Emp 03 module - Department and Role (you might have to build it later probably)
Your formula should be
IF Employed? THEN Salary * 'EMP01 Employee Drivers'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Country] ELSE 0
Make sure you refer the line items while building the formula and avoid writing the names of line items manually.
Hope that helps
Misbah
0 -
Hi @rahulharjai
I believe there is a mistake in the instructions.
I training someone recently with this same issue.
The formula should be the following:
IF Employed? THEN Salary * 'EMP03 Employee Drivers By Role'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0
The country look up does not apply here.
I hope this helps,
Usman
1 -
HI @usman.zia,
Thank you for your prompt response. Yeah It seems to be a mistake in the instructions.
But We don't have EMP03 Module 'Employee Drivers by Role' in our Model. I tried this mentioned formula and that's why received an error for EMP03 "not a recognized line Item or list member".
Please suggest the steps to create this EMP03 Role module first in order to rectify this . Thanks in advance.0 -
Hi @Misbah,
Thank you !
I had checked it for the same while building the formula and referred the line items but with no luck on this issue.
Thanks & Regards0 -
Hi @Misbah,
Hi @Misbah,
Just a thought I want to share :
can we create a department & Country line item in EMP02 Module itself and assign format as list E1 Department & G2 Country and make them available in EMP02 module itself and then refer them directly in formula from EMP02.
Need your expert opinion on this & please suggest some other suggestion.
Thanks & Regards0 -
I still am not convinced that the formula didn't work - it has to work given the dimensions are correct.
To answer you question -You can do that but I wouldn't recommend you do that because it goes against DISCO (simply put best practices) Also you already have a connection between Employee, Department and Country, there is no need to have these line items in the module
Can you please post screenshots of your target module & formatting of your mappings. I place my bet on the mappings.
0 -
Hi @Misbah,
Please find the attached screenshots of Modules and format of mappings.
Regarding the query : Thank you for valuable inputs ! will take into consideration.
Thanks0 -
Can you please confirm what are the dimensions of Emp 02 module as it is not visible in the last screenshot
Misbah
1 -
Hi, I am having the same issue the formula is simple, I just move the focus to EMP03 I am receiving the following error:
invalid formula: Roles hierarchy is not possible as Roles does not have a built-in top level
To overcome this, I have named the top level All Departments as per E1 Departments
However, now I have a data type mismatch: format other than LIST or DATE being referenced as a relation.
EMP02 Formats
Roles within SYS08 format text
Department within SYS08 List E1
Can you advise, I believe the error is within the top level?
Really Appreciated
Kind Regards
Jo
0 -
I believe there are steps missing in the instructions or they are not in the correct order.
You have to think about what you want to achieve and whether it makes sense.
There is a data file that contain the relationship of both roles and departments.
This is what is required to create the module EMP.03 Employee Drivers by Role.
This will contain 4 line items:
Bonus %
Car Allowance
Phone Allowance
Medical Benefit
This module is only used to store data / assumptions therefore requires an import of the data to be made and nothing else.
This module is not dimensioned by time either.
Then in the module SYS.08 Employee Details there are the relationships required to make the lookups for role and department.
This is because the module EMP.02 Employee Expenses is dimensioned by the subset of active employees and the source data is stored in a module dimensioned by role and department.
SYS.08 Employee Details contains the relationships required to bring in the data into EMP.02 which are lookups on Department and Role.
Hence the formulas for:
Bonus - IF Employed? THEN Salary * 'EMP03 Employee Drivers By Role'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0
Car costs - IF Employed? THEN 'EMP03 Employee Drivers By Role'.Car Allowance[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0
Phone costs - IF Employed? THEN 'EMP03 Employee Drivers By Role'.Phone Allowance[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0
Medical costs - IF Employed? THEN 'EMP03 Employee Drivers By Role'.Medical Benefit[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0
To know you've done it correctly in EMP.02 for Sindy Wager here bonus in FY20 should be 6210.
I hope this is clear and if you have anymore questions please do let me know.
Thanks,
Usman
5 -
0
-
Hey Usman,
Thank you, my lookups are working now, brilliant, my data type was incorrect.
Apologies I am working on the next section REP04 Employee Expense Summary, I have set up the module, no worries, I am struggling with the formula, if I were calculating within excel I would opt for a SUMIF, which within Anaplan is a SUM function. I am trying to complete this using SYS08, is this the correct approach:
Do I need to create a sub model as per EMP03 and EMP02 to complete a SUMIF from SYS08 Data?
Really Appreciated
Kind Regards
Jo
0 -
Dimension seem to be matching. Can you please follow what @usman.zia has explained. I think it appears to be ordering of the instructions.
Thanks,
Misbah
0 -
Hi @Joruss75
For the module REP04 the modules dimensions (lists) are G1 region and role.
Therefore you need to sum on the region and the role and pick up the values from EMP02 Employee Expenses
An example formula for the salary would be:
'EMP02 Employee Expenses'.Salary[SUM: 'SYS08 Employee Details'.Region, SUM: 'SYS08 Employee Details'.Role]
Here we use SUM instead of LOOKUP because we are aggreagting on the dimensions.
I hope this helps!
Thanks,
Usman
2 -
Hi Usman,
Thank you, I believe that is working now?
Kind Regards
Jo
0 -
Hi @usman.zia,
Thank you !
I had created a EMP03 Module "Employee Drivers by Role" as instructed. Please find the attached screenshot for the same.
Now where we will get the Data file to import into this module and we don't have role as a line item.What relationships we need to create in SYS.08 Employee Details Module to resolve this? Before using this below mentioned formula,
Bonus - IF Employed? THEN Salary * 'EMP03 Employee Drivers By Role'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0
what other steps I need to follow to build the relationship in SYS.08 Employee Details.
0 -
Hi @rahulharjai
I have attached an image of the blue print for the sys.08 module.
You will need to import the data for line items that have no formula and make sure you have used the correct line item formats to enable the formula to work correctly.
I hope this helps!
Thanks,
Usman
1 -
A couple of things, you should not have subsidiary views defined (the little icon after the line item in your picture). That happens when the dimensionality of the line item is not the same as the what has been defined for the module. Secondly, I am not sure where E3 Roles list came from, Roles should be a flat list. Lastly, the module should look like the below.
2 -
@rahulharjai @Misbah Something is incorrect with the instructions. I have encountered the same exact issue.
From what I understand from the discussion, one possible solution is building a module titled EMP03. This step is not in the instructions and should be done on an Ad-hoc basis.
0 -
The issue I have with the EMP02 module is that the lookups for the car, phone and medical benifits line item is not working properly because the EMP01 module does not include the time dimension. So instead of getting a single value for these line items, I get an aggregation, for example for Sarah Engle, I get car cost of 1.8M instead of 800. I don't know if this is addressed later in the course or not but I haven't figured out a way around it yet. Any ideas?
0 -
Update: Discovered that the list used for EMP02 was a sub-list of G3 Location instead of #E2 Employees. Correcting this has corrected the dimension error, which makes sense. I'm unsure if the instructions are incorrect as I have not needed to create an additional module titled EMP03. All seems to be right.
0 -
Hi Usman -
I am having a similar issue than Joruss below. When I try to build the formulas in EMP02 for the bonus, car allowance, etc I get the message: "Data type mismatch: format other than LIST or DATE being referenced as a relation".
I understand this to be an issue with my EMP03 data type not matching something, but unlike Joruss I can't figure out how to fix the data type.
Attached are the screenshots for the various modules and error message.
Thanks in advance for your help,
RP
0 -
@jai.kannan
Could you please provide the detailed steps so that we don't need to create an additional module titled EMP03.
Thanks0 -
Hi, I am doing the same thing right now and I am getting the same error. Do you by any chance recall which of your datatype was incorrect?0
-
Hi Usman,
I tried this formula IF Employed? THEN Salary * 'EMP03 Employee Drivers By Role'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0 but it says the below:
Invalid formula for 'EMP02 Employee Expenses'.Bonus: Automatic sum of 'EMP03 Employee Drivers by Role'.Bonus % over Role hierarchy is not possible as Role does not have a built-in top level
Can you please help?0 -
Hi Usman,
I got confused reading the conversation about the tips to solve the Level 1 certification.
When updating the formulas of EMP02 (Bonus & costs of car, phone and medical), the following message pops up :
Invalid formula for 'EMP02 Employee Expenses'.Bonus: Automatic sum of 'EMP03 Employee Drivers by Role'.Bonus % over Role hierarchy is not possible as Role does not have a built-in top levelMay you help ?0 -
Hi I have done what you said and checked Sindy which was correct, but the figure I get for Medical for Finance in Dec is wrong. 36800. I am doing something wrong I just dont know what
0
