Apex Code Development You need to sign in to do that
Don't have an account?
OnCloud9 Rolling 12 month average
Hi Folks!
I'm stuck on an issue and need community assistance! =)
What I'm trying to accomplish is this: I have 2 custom objects, called Parent and Child. A parent can have many childs, via related list.
On the child record, we capture data per given time frame. (i.e. Start Date = 1/1/2011; End Date = 1/31/2011, Total cost = $50, etc.)
What I'd like to do is take the rolling average of the child's costs (i.e. if today is 5/25/2011, then look between today and 12 months ago, and tally up 1) number of records (aka months) and 2) total costs. Then do the average math (costs/month) to achieve the final result.
I'm familiar with formula fields and roll up summary, but not so much with apex.
Does this require apex (i assume it does to get the between "Today ---> prior 12 months") and how would I attempt to do this? I think I have the logistics down but am running into some trouble trying to make it work in SFDC.
Your help is HIGHLY appreciated and your knowledge is invaluable to my learning. Thanks for your help in advance.
I think you can do this without any apex, but you may have to get creative.
If you create a Roll-up summary field with the 'Roll-up Type' of SUM and add a filter setting with [field] -> your date field, [operator] -> equals, [Value] LAST 365 DAYS
then, create another roll-up summary field with the 'roll-up type' of COUNT to count each child record with the filters set again to only pull those records created in the LAST 365 DAYS and finally, create your formula field where you use the values from both roll-up fields to calculate your rolling 12 month average.
hope that helps.
-Victor
Thanks for the note! I thought about this but I have a "what if" statement.
What if we don't have 'up to date' data. As in, today's May but we only have statements from Last May - Jan. Then it's gets more complicated right? Because we are no longer looking from (May 2011 ---> May 2010), but rather, (Jan 2011 ---> May 2010). Any idea on how to tackle this? Happy Friday! :)