Level 3 sprint 1

andis
andis
in Academy Discussions

Hi Experts,

A couple of weeks ago, I imported data into the below module.

andis_0-1615443797141.png

 

Then, I summarized the above data into the below module.

andis_1-1615443845682.png

 

I have no clue to get data PY Country and Product Family Sales in the below module.

andis_2-1615443937004.png

 

Please advise me.

 

Regards,

Comments

  • JaredDolich
    JaredDolich

    @andis 

    You're probably closer to the answer than you think. They key is to take another look at SYS03.

    Here's the correct formula:

    'TAR10 Actuals Country and PF'.Sales Actual[LOOKUP: 'SYS03 Account>Product Details'.'P1 Product Family', LOOKUP: 'SYS03 Account>Product Details'.'G2 Country']

    Let's break it down.

    • TAR01 is dimensioned by A2 Account > Product.
    • And our SYS03 is also dimensioned by the same list.
    • We need a way to map country and product family to the A2 Account> Product list. We do that in SYS03.
    • So you can get from TAR10 the Actual Sales and bring it to TAR01 by using the SYS03 module.

    Always remember that when you use LOOKUP and SUM, you have to use a list formatted line item.

    Hope this helps! You got this!

  • andis
    andis

    @JaredDolich 

    I didn't see the TAR10 The Actual Sales module in the Level 3 model start. How to download this module?

     

    Regards,

     

     

  • JaredDolich
    JaredDolich

    @andis 

    As part of L3, we're not told what to name our modules. In chapter 5, you should have built a module that contains the actual sales, I just happened to name mine TAR10. Perhaps you named it differently than I did. Take another look at your modules, it should be there. Look for a module that has country and product family as lists.

  • andis
    andis

    @JaredDolich 

    I assigned Product Family list and Country list in the TAR01 Detailed Sales Targets module to link the TAR10 module without the SYS03 module. What do you think?.

     

    Thanks,

  • JaredDolich
    JaredDolich

    @andis 

    That will work but it's not the most efficient way. The best way is to follow the DISCO methodology and use the system module, in this case SYS03. By putting the line items in TAR01 you are essentially calculating the same value over and over, placing a burden on the calculation engine, not to mention you'll take up more memory than necessary. By using the SYS03 module you calculate once, and the reference it from TAR01. 

    Hey, you're on the right track, first step is to get it to work. In L3 this is great time to explore how to optimize the model. Try using SYS03, you'll see it works great. You can use the formula I gave you above!

    Great job!

  • andis
    andis

    @JaredDolich 

    When I checked the below report VS Anaplan's screenshot, I saw something wrong in Brazil data below.

    andis_2-1615474317352.png

     

    And my stretch goal is below:

    andis_1-1615474240259.png

     

    Brazil data is 1 in Anaplan's screenshot. 

     

    Did you get the same result in your level 3 checking report?

     

    Regards,

  • Considine
    Considine

    Thanks for the information keep sharing such informative post keep suggesting such post.

     

     

    myccpay

  • ssicefox
    ssicefox

    Hi, @JaredDolich 

     

    How are you?

     

    I created the SYS14 system module. Could you please tell me if my formulas are correct?

     

    ssicefox_0-1629466457165.png

     

     

    ssicefox_0-1629466823212.png

     

    I am looking forward to hearing from you soon.

    Thank you.

     

    Best regards.

     

    Wei

  • JaredDolich
    JaredDolich

    Pretty close. Everyone will have a slightly different answer but here's how I approached SYS14 -

    • for Data? I used <> since it's technically possible the sales are negative.
    • For code you may want to add a conditional to make sure it's valid.

    SYS14_1.png

  • ssicefox
    ssicefox

    Hi @JaredDolich @andis,

    I am having trouble with the formula in the DAT02 PY Revenue to CY module.

    I wrote a formula following to Import Revenue FY19 into DAT02 PY Revenue to CY (dimensioned by A1 Accounts, P2 Products and Time Range)

    'DAT01 PY Revenue'.Revenue[LOOKUP: 'SYS03 Account>Product Details'.'A1 Account', LOOKUP: 'SYS00 Time Settings'.PY Qtr]

    ssicefox_2-1629501430083.png

    Then  I received a result following:

     

    ssicefox_1-1629500532007.png

     

    Seems to be aggregating at the account level for each product.

     

    Any guidance would be greatly appreciated!

     

    Best regards.

     

    Wei

     

     

  • Misbah
    Misbah

    @ssicefox 

     

    Since it is Level 3 we usually don't share the answers & I think this one is simple - Check the dimensionality of Source Module and see how many LOOKUPs are required. I think you are missing one LOOKUP

     

    By the way, A2 Account-Product# has to be the dimension of the entire module. Get rid of that subsidiary view.

     

    Misbah

  • ssicefox
    ssicefox

    Hi, @Misbah 

     

    Thank you for your hint.

     

    I tried 3 lookups (A1 Account, P2 Product and PY Qtr) together, but I received nothing. I think I should import data of P2 Product into the SYS03 before looking up.

     

    Best regards. 

  • Misbah
    Misbah

    @ssicefox 

     

    You need to have Accounts, Products line items in SYS module for A2 Account-Products#  populated before you LOOKUP.

     

    Misbah

  • ssicefox
    ssicefox

    @Misbah 

    The P2 Product data isn't showed in SYS03.

    ssicefox_0-1629535366295.png

     

    ssicefox_1-1629535429341.png

    I tried to add a formula for P2 Product, but I am not successful.

     

    Best regards.

     

    Wei

     

  • Misbah
    Misbah

    @ssicefox 

     

    You are very close. You will have to populate "Product Flat" Line item first and then use Product Flat Line item to update P2 Product Line item.

     

    Misbah

     

     

  • ssicefox
    ssicefox

    Hi, @Misbah 

     

    Thank you again for your hint. I populated "Product Flat" Line item.

     

     

    ssicefox_0-1629541762725.png

     

    But P2 Product doesn't have any data as well.

     

    ssicefox_1-1629542240304.png

     

    Best regards.

     

    Wei

     

     

     

     

  • Misbah
    Misbah

    @ssicefox 

     

    As I said earlier you need to reference Product Flat line item, It looks like you are referencing ID line item in your formula within P2 Product

     

    FINDITEM(List, Name(Product Flat))

     

    Misbah

  • ssicefox
    ssicefox

    @Misbah 

    Thank you for your help.

    I got it.

     

    Best regards.

     

    Wei

  • SuhasDesai
    SuhasDesai

    Hello @JaredDolich 

     

    When i am trying to enter the formula for Code, getting below error-

     

    SuhasDesai_0-1631544830837.png

     

    Could you please advise?

     

    Thank you. 🙂

  • ssicefox
    ssicefox

    @SuhasDesai 

    You lost " ELSE".

  • ssicefox
    ssicefox

    @SuhasDesai 

     

    IF THEN ELSE

  • Misbah
    Misbah

    @SuhasDesai 

     

    There is no ELSE statement.

     

    I could see

    IF xxxx THEN yyyy 

    it needs to be 

    IF xxxx THEN yyyy  ELSE zzzz

     

    Misbah

  • ssicefox
    ssicefox

    Hi @Misbah , 

     

    @JaredDolich 

    How are you?

    I am trying to upload the CYW Scenario Territory to Accounts.CSV into the Territory>Account# List, but I did find a solution to concatenate Territory code and Account code for filling the Code column.

     

    ssicefox_0-1631610027609.png

     

    Any hint/clue is much Appreciated.

     

    Thanks in advance!

     

    I am looking forward to hearing from you soon. Best regards.

     

    Wei

  • sdavidko@ciena.com
    sdavidko@ciena.com

    In the Product Flat line item - how did you use display name with a list formatted line item? When i tried that, it gave me error that i can only use display with text formatted line item. How this worked for you?

Categories