Formula Help: Apply Rev. to Earliest Date

cpalm2

Hi,

I need to create some logic where revenue that is allocated to a parent, is all placed onto the child with the earliest date associated with it.

E2EJonathan

Without knowing much detail, I assume their is a date column for all the children called something like "Start Date".  Make a new LI which uses Rank to find the newest and break ties using sequential with something like: RANK("Start Date", ASCENDING, Sequential, TRUE, a Text LI which is the same for each group of Children).  Then in another LI have an IF Statement so if the Rank = 1 you assign that product all the revenue.  Note that Rank is something you should try to avoid if you can find another solution.  You could try a Min on Start Date and then check to see if the parent start date equaled the child start date. in a another LI too if you aren't worried about 2 children having the same start date which would be more efficient.

 

 

chofacker

Hi!

Can you provide more context to what you mean by earliest date associated? Also, are you assigning 100% of the revenue to one child of a parent or to multiple?

Thanks,

ChrisAHeathcote

@cpalm2 

Create module dimensioned by the child.

Create line items including parent (parent list format ), date ( date format ) and phasing ( number format )

Use the following formula for the parent line item =PARENT(ITEM(CHILD LIST))

Allocate the associated date to each child item. Set the summary method to Min.

Use the following formula in the phasing module = IF date[LOOKUP:Parent] = date then 1 else 0

This will allocate a 1 against the child item whose date matches the min summary at the parent level.

You can then use this to phase the parent value down the child in a target module dimensioned by the child list.

Be mindful that if you have more than one child item with the same date you will duplicate the phasing across multiple child list items.

cpalm2

This worked perfectly. Don't know why i didn't think to use the rank function!

anna.koerber

Solution on Earliest Date with multiple children in context of comp plan dates.

My use case was in context of sales planning when mid-year adjustments occur on a reps plan a new child would be created. To determine what type of change occurred the MIN summary worked great for comparing changes across all child records. 

 

1. Add "min" summary to the date line item in your module dimensioned by the child list

2. In a parent dimensioned module reference back to that date line item.

From there, I did a lookup off of the line item in the parent module to compare my new plan dates in the child module. 

----

Child Module:

Name (Parent list is used)

Start Date (MIN summary method)

 

Parent Module:

Start Date: child.start date (<- takes the min date)

 

=> Child Module: If you were doing an output for booleans, dates, etc., you'd need to use lookup off the name.

Earliest Date?: Parent.start date[lookup: name] = start date