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.

[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,

[E2EJonathan · Accepted solution]

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.

[cpalm2]

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

[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.