Parent to Child Lookup/Selection

michael.chefer

 

 

Hoping someone can help me with this one.

 

The target line items are Split 1, Split 2, and Split 3. The target module is named 'Sales Pursuits' and is dimensioned by a list named 'Pursuits.'

 

The source line item is 'Split Rank'. The source module is named 'Pursuit Team Split' and is dimensioned by 'Team Members' which has a parent hierarchy of 'Pursuits.'

 

I'm having trouble populating the line items Split 1, Split 2, and Split 3. The logic for these line items is as follows:  

 

Split 1 = IF  'Pursuit Team Split'.'Split Rank' = 1 then  'Pursuit Team Split'.Split Amount

Split 2 = IF  'Pursuit Team Split'.'Split Rank' = 2 then  'Pursuit Team Split'.Split Amount

Split 3 = IF  'Pursuit Team Split'.'Split Rank' = 3 then  'Pursuit Team Split'.Split Amount

 

The problem is that I'm struggling to get the formula to select the correct split ranking and amount. I think this has to do with the fact that I'm dealing with a parent hierarchy. 

VIGNESH.M

Hi @michael.chefer ,

 

You are trying to bring data from the child(Pursuit) to the parent(Team Members) list, but you want to aggregate based on Rank.

To make it simple,

I suggest you create a list called Split(Split 1,2&3) and create a line item(Split) in the Pursuit Team Split module and map items based on the rank line item.

For Rank 1 -> Split 1, 2- -> Split 2...so on. you can create this mapping by a formula.

 

Your Target module should have (Pursuit, Split) dimension and line item called Amount. 

Now you just have to aggregate the Split amount by Split and Pursuit dimension.

 

Amount = 'Pursuit Team Split'.Split Amount[SUM:Pursuit Team Split.Split, SUM: Pursuit Team Split.Pursuit]

 

Regards,

VM

Misbah

@michael.chefer 

 

Yes, it has to do with the hierarchy and the reason is that you have kept your Summary turned off on Rank Line item which is rightly done. If you have to pull up the values you will have to play around with summary options of your source module But you can not have multiple summary settings of a single line item - probably you may have to create as many line items as there are ranks (Assuming there are only 3 ranks)

 

Alternatively, You can also get rid of the Pursuits dimension in the target module and create line items for each list member and use LOOKUPs to pull the value  (again assuming you do not have too many ranks to play around with)

 

 

Misbah

@VIGNESH.M Yours is better Solution.

 

One correction though. You don't need to use SUM on Pursuits as it will auto aggregate.

VIGNESH.M

Yes @Misbah, SUM on Pursuits is not required, as it will auto aggregate at Parent(Pursuit) dimension.

 

Pursuit Team Split.Amount[SUM: Pursuit Team Split.Split]