Question 6 in level 1 model building exam

Options

Could anyone who has finished the exam show me the formula for question 6? I failed 2 questions for this part.

I think I just need to refer the relevant numbers from another module and apply selections, and then it could be done. However, my way didn't work.

Here's my formulas. Thanks very much!

4c56da1c45b1f6ce26b2ded2718ee74.png

SharedScreenshot.jpg

Tagged:

Comments

  • Hi, you will need to use lookup function as the answer requires both region and role.  

  •  @titanic_neo 

    Also another hint. You're very, very close.

    Use the SUM function on Region and Role from your SYS08 module. 

    Breakdown:

    1. YOu need Medical costs from EMP02 which uses Employee as a dimension but your TARGET module, REP04 is dimensioned using Region and Role.
    2. So you need to convert your employee dimension to region and role. Your SYS08 does this! 
    3. Remember, you always use SUM on a list formatted line item.
    4. So your formula will look something like this.  EMP02.Medical Costs[SUM: SYS08.Region, SUM: SYS08.Role]

    You got this!

  • I passed the exam without getting the answer to this question my formula: 

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

     

    The result was 21,600 for FY21. This was not the correct answer. Did I do my formula wrong?

    funny enough the I got next question America, Executives, Bonus, FY21 correct using the value in the module.

  • Check REP03 module .

  • tspoon
    Options

    @JaredDolich 

     

    How would I go about adding Department and Role to SYS08? I don't believe that was a part of the exercises which I followed step by step. This is what I have for my SYS08:

     

    Screen Shot 2022-03-15 at 5.54.40 PM.png

     

    I have Department and Country...

  • nileshs
    Options

    I used the same formula mentioned but the results is blank.

    I see no data in sys08 region and role. they are formatted as list G1 and E3

  • Moved post to Academy & Training forum.

     

    FYI @titanic_neo @nileshs @tspoon @BharatKDeshmukh @HXTrainer360 

  • Hi,

    I had many difficulties with this question 6, spent roughtly 2hours. I got the correct answer with:

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

    I tried with LOOKUP but didn't work, don't understand why.

    Actually I understand my current formula as:

    Get in EMP02 the medical cost per employee (calculated with EMP03 drivers) and make a summary of this cost category in regards of the Region and the Role i chose in the selectors.

  • This is not working, Please guide:

  • I have also tried to get data from 'REP03 P&L report'.Medical Costs, but it gives wrong answer.

  • With reference to SYS08 Module the format type for Role is "Text" .

    The Error message is 'Data Type Mismatch' and its not accepting data type other than list/date