TAR01 Detailed Sales Target module

Highlighted
Occasional Contributor

TAR01 Detailed Sales Target module

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! 

 
7 REPLIES 7
Highlighted
Frequent Contributor

Re: TAR01 Detailed Sales Target module

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

Highlighted
Occasional Contributor

Re: TAR01 Detailed Sales Target module

@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'. 

Highlighted
Frequent Contributor

Re: TAR01 Detailed Sales Target module

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

Highlighted
Occasional Contributor

Re: TAR01 Detailed Sales Target module

@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. 😊

Highlighted
New Contributor

Re: TAR01 Detailed Sales Target module

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 

Highlighted
Occasional Contributor

Re: TAR01 Detailed Sales Target module

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.  

Highlighted
Contributor

Re: TAR01 Detailed Sales Target module

@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