TAR01 Detailed Sales Target module

SaraBTIT
SaraBTIT
in Academy Discussions

In the description from '5.2 Model Build Specifications', we need to calculate the proportion of a country's total sales revenue by product family each account's product sales contributed. The TAR01 was created already in the model, it's dimentioned "A2 Account>Product#' only. However the first line item is to calculate PY Country and Product Family sales, shall I personally add another dimension 'G2 Country' to the module?  I tried to calculate it by using DAT02 by referencing SYS03, it was incorrect in anyway "Level mismatch on common dimension". Please help! 

 
Tagged:

Comments

  • abhi1017
    abhi1017

    Hi Sara,

    Additional dimension of G2 country would not be required,instead create two more line items in the TAR01 module to map country and product family (from SYS03 Account>Product Details).

    To find PY Country and Product Family Sales : 'DATA 02 PY Revenue to CY Module'.'Historical Revenue ''20'[SUM: 'SYS03 Account>Product Details'.'P1 Product Family', SUM: 'SYS03 Account>Product Details'.'G2 Country', LOOKUP: Country, LOOKUP: Product Family]

    Attached is a screenshot for reference.

    Note: Sum and lookup is not a best practice.

    Hope it helps

    ~Abhi

  • SaraBTIT
    SaraBTIT

    @abhi1017 Appreciate your guidance. I added Product Family and Country as line items in TAR01, and tried the formula as you suggested, it still gives error 'Level mismatch on common dimension'...my DAT02 got two dimensions 'A1 Accounts' and 'P2 Product'. 

  • abhi1017
    abhi1017

    abhi1017_0-1591938376690.png

    Create line items in the DATA 02 module as well to map country and product family.

    These lineitems should be created with specific dimensions.

    Attached is screenshot for reference.

     

    ~Abhi

  • SaraBTIT
    SaraBTIT

    @abhi1017 thanks a lot! I finally changed the dimension of DAT02 to "A2 Accounts>Product#", by this way I don't have to add the line item Product Family and Country. 😊

  • bleeds
    bleeds

    Hey I was wondering if you can shed more light on the order of operations you used?  When loading the historical data from the data hub into the sales planning model, should we be using the Account>Product list# and time as dimensions?  Originally I created a DAT01 Historic Volumes Module and used Products & accounts as my dimensions. Then Loaded the requisite data into this module and used a lookup function referencing DAT01 in the DAT02 module.   

    Before I get too far - I'd like to ensure that my approach is correct.  I see that you changed the dimensionality of the DAT02 Module to be the product # list, but I'm having trouble loading the original historic volume data into a module with only the numbered list dimension.

     

    @abhi1017  @SaraBTIT 

  • SaraBTIT
    SaraBTIT

    Hi @bleeds , when loading historic data, DAT01 is supposed to have the same dimension as the module in Data Hub; For DAT02, I used A2 Account>Product# as dimension, you can also use the same dimension with DAT01 but it would require more line items to be created (as @abhi1017  advised) and lookup those line items in TAR01 module.

    If set the list A2 Account>Product# as dimension of DAT02, your approach is correct to reference DAT01 and lookup SYS00 & SYS03, by this way, it can facilitate some formulas in TAR01 module.  

  • sosunkwo
    sosunkwo

    @abhi1017, In my SYS03 Account>Product Details module, I can't seem to populate my P2 Product. Everything I have tried returns a blank - doesn't show any data. I wonder what I'm doing wrong. 

     

    I've tried to getting the ITEM('P2 Product) = shows nothing. 

    6.PNG

  • abhi1017
    abhi1017

    @sosunkwo 

     

    The dimension of the module isn't mapping to the formula being entered.

    Dimensionality of your module is a concatenated list of accounts and products i.e. A2 Accounts>Product# list rather A1 account and P2 product lists.

    Check the screenshot attached above in the trail for reference.

     

    ~Abhi

  • jakesachs
    jakesachs

    I know this is from a while ago, but this is flabbergasting to me....I did the equation and essentially you are doing a sum and lookup of the same 2 things. Ive seen sum lookups, but never Sum X Lookup X sum Y lookup Y. Any idea why that works?

  • rajesh.tsv@cognizant.com
    rajesh.tsv@cognizant.com
    Yes, I created an intermediate module with G2 Country and P1 product Family and time dimension for loading prior revenue . Through this method, I was able to keep sum and lookup separate.
  • tinathom
    tinathom
    Thank you @abhi1017
  • Avishi
    Avishi
    Try Using FINDITEM('P2 Product', RIGHT(Code,4)), Here the P2 Product is the list and not the line item.
  • neg177
    neg177
    I had the same issue, but I resolved it. message me ack if you would like to see how I approached it.

    Thank you

Categories