Level 1 Exam Activity 5

WanchiS
WanchiS
in Academy Discussions

Hello Anaplan experts,

 

I am having issue with the activity 5.  I tried using this formula:

 

IF Employed? THEN 'EMP03 Employee Drivers by Role'.Medical Benefit [LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0

 

However, I got an error message:  "Invalid formula for 'EMP02 Employee Expenses'.Medical Costs: Automatic sum of 'EMP03 Employee Drivers by Role'.Medical Benefit over Role hierarchy is not possible as Role does not have a built-in top level"

 

What does this mean and what can I do to fix it?

 

Thank you!

 
 

Comments

  • chofacker
    chofacker

    Hi @WanchiS 

     

    Think of your top level being something that all list members roll up to if you do not have a parent. This is required to sum over the dimension Role. Review this https://help.anaplan.com/anapedia/Content/Modeling/Build%20Models/Lists_and_Roll-ups/General_Lists.html for more information on how to add a top level to your list. Try the formula again and it should work.

     

    Let me know if you still have questions.

     

     

  • einas.ibrahim
    einas.ibrahim

    @WanchiS 

    First, are you referring to an exam or an activity question? if your question is regarding activity in the training please provide a link or a screenshot of what the activity is asking. We can't help with exam questions but we can help you understand a concept.

     

    Now, what @chofacker said is not wrong and the meaning of the error is that you are trying to LOOKUP on a list that doesn't have a parent or a top-level, however, adding a top-level is not always the solution. You may be using the LOOKUP incorrectly. 


    There are many posts on the community that help with understanding how to construct a correct LOOKUP.

    These are 2 posts I have written before, you may start from here...

    1. https://community.anaplan.com/t5/Anaplan-Platform/Level-2-Conclusion-DEM04-Demand-Export/m-p/77426#M...
    2. https://community.anaplan.com/t5/Academy-Training/Altering-Line-Item-Formula-with-lookup/m-p/88677#M...

    If the above posts are not clear and you couldn't find other helpful posts on the community then please let me know and I'm happy to walk you through how to construct a correct LOOKUP.

     

    Good Luck 

  • WanchiS
    WanchiS

    Thanks so much for your help!  I added the top level to the "Role" list, and tried updating the formula again, but got another error message, saying "Data type mismatch: format other than LIST or DATE being referenced as a relation".  I have a disconnect here because:

    1) it does not make sense that a "Role" list to have "list" or "date" as the format, so I'm not sure why the error message is referring to them?

     

    2) the formula I have constructed does not include the "Role" list in its reference (neither do the Target module (EMP02) or the Source module (EMP02) or the Mapping module (SYS08) referenced in the formula, so I am unsure how adding the top level to the "Role" list would correct the error?

     

    IF Employed? THEN 'EMP03 Employee Drivers by Role'.Medical Benefit [LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0

     

    Thank you so much!

     

    WanchiS_0-1604010755728.png

    WanchiS_1-1604010905578.png

    WanchiS_2-1604011150854.png

     

     

     

  • WanchiS
    WanchiS

    Thanks Einas! Your explanations are very helpful. I believe I have the correct lookup formula, which is very similar to the solution discussed in your 2 other posts:

    IF Employed? THEN 'EMP03 Employee Drivers by Role'.Medical Benefit [LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0

    I added the top level to the "Role" list, and tried updating the formula again.

    I now get a different error message, "Data type mismatch: format other than LIST or DATE being referenced as a relation", which I am trying to resolve.  Would you have any tips or advice?  Thank you!

  • einas.ibrahim
    einas.ibrahim

    Hi @WanchiS 

    May I ask why did you add the top level for the Role list?

  • chofacker
    chofacker

    @WanchiS  

     

    You are on the correct track now! While, @einas.ibrahim is correct and we should not give away answers, I can tell you a couple things to think about while using LOOKUP.

     

    1. First, keep in mind the syntax and accepted input, mapping and output formats for LOOKUP. Review Anapedia Notice that you can only have a mapping x[LOOKUP: y] which is your Y value that is a time period or list format. Double check your model to be sure this is correct.

    2. Ensure your data value, which is X in x[LOOKUP: y] and ensure your format is the same as the target or place you want your answer to display. 

     

    Another note about Top Levels - Because Role is a composite list and does not have a parent in the hierarchy, LOOKUP will require a top level which resolves the issue of your list Role not having a parent. This is a constraint of LOOKUP which you can review in Anapedia. 

     

  • JulieBurgess
    JulieBurgess
    @WanchiS wrote:

    Thanks Einas! Your explanations are very helpful. I believe I have the correct lookup formula, which is very similar to the solution discussed in your 2 other posts:

    IF Employed? THEN 'EMP03 Employee Drivers by Role'.Medical Benefit [LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0 Official Site

    I added the top level to the "Role" list, and tried updating the formula again. 

    I now get a different error message, "Data type mismatch: format other than LIST or DATE being referenced as a relation", which I am trying to resolve.  Would you have any tips or advice?  Thank you!

    I faced the same issue, still facing that, no solution yet.

Categories