In tab 7.08 I would like and additional column between actual total cost and planned cost on tab 7.0

Arjie

Arjie

In tab 7.08 I would like and additional column between actual total cost and planned cost on tab 7.08. The column should be Actual Program FY just like on the lower screen print. This column should indicate the total expenses for that WBS that are in the fiscal year that the WBS was created.

Can someone help me with to interpret this as formula in the new line item "Actual Program FY" that I should create.

TristanS

@Arjie We would require more information. You would need to provide the following:

1. What are the dimensions of the source module in tab 7.08
2. What are the dimensions of the source module in the Program & Creation tab
3. What is list hierarchy of the dimensions between item 1 and 2. I'm assuming that 1 and 2 are related somehow the form of child/parent list

Arjie

1. What are the dimensions of the source module in tab 7.08 ans: Sales program, Line items
2. What are the dimensions of the source module in the Program & Creation tab ans: Marketing programs, line items
3. What is list hierarchy of the dimensions between item 1 and 2. I'm assuming that 1 and 2 are related somehow the form of child/parent list

Thanks..

TristanS

@Arjie The relationship between Marketing and Sales program based on the information you have provided is not straight forward as either is a parent of the other.

1. Do you have a line item that defines the relationship between sales and marketing program? For example, in a Sales program module have a line item of format "Marketing Program" such that you something like:
   1. Sales Program 1 maps to Marketing Program X
   2. Sales Program 2 maps to Marketing Program Y … etc
      If you do then execute a lookup formula: E.g. Marketing Program module.Actual Program FY[LOOKUP: Sales Program Module.Marketing Program]. If you don't have this information then it would be best if you are able to derive it somehow.
2. To be able to derive it, you first need to establish what the relationship between sales and marketing programs.

• one to one. One marketing program can have only one sales program AND one sales program can only map to one marketing program.
• one to many.
  • One marketing program can have one or many sales programs BUT a sales program can only map to one marketing program OR
  • One sales program can have one or many marketing programs BUT a marketing program can only map to one sales program
• many to many.
  • One marketing program can be associated to many sales programs AND one sales program can be associated to many marketing programs

3. Answer to item 2 will determine the dimensionality of the mapping module between sales and marketing programs. Easiest would be one to one and you could potentially use an existing module you already have or create a module which has one dimension. For your intentions it would only be dimensioned by sales program. Worst case scenario is many to many in which case you may need to have a mapping module dimensioned by both marketing programs and sales programs. If it is one to many, would it be possible to have the relationship as one to one if you capture mapping at a lower level of the hierarchy, e.g. Promoted Products? If yes, you would just need a mapping module dimensioned by Promoted Products otherwise you will need to have your mapping module dimensioned by both Marketing Programs and Sales Programs.

4. Now how do you derive it. I'm inferring the following relationships (which may be wrong) based on the data you have provided.

• C3 province seems to be a common attribute between Marketing Programs and list hierarchy Sales Programs. I'm guessing C3 is part of a location hierarchy?
• If P4 PGC Abbreviation and P6 CSPC is part of the same list hierarchy I'm guessing it's product hierarchy?
• What you can potentially do is map what a marketing program a sales program is associated to by matching the following:
  • Effective dates. Does the sales program dates fall inside the marketing programs start/end month
  • Products. Does the sales program cover the products in the marketing program
  • Location. Does the sales program cover the locations in the marketing program. It might get tricky when you get more than one

Not really sure if my response addresses your problem but hope this helps.

Arjie

Ok thanks a lot, this is such a big help.