As a child (master-detail) to my opportunity object, I have a custom object 'Transactions'. Each transaction has its own interest rate, transaction date, and amount. The opportunity has a term (in months and years - ensured by workflow rule), and interest period (in months).
On the opportunity, I would like to do three things:
- I would like to sum the total transaction amounts on the opportunity (done)
- I would like to calculate the weighted average interest rate of the opportunity
- I would like to calculate the total accrued interest on the opportunity
1 is already solved because the amount field can be summed via a rollup on the opportunity
2 is proving problematic because there is no 'weighted average' formula on rollups. What I could do is calculate the (transaction.rate)*(transaction.amount) on each transaction, then create two rollups on the opportunity; one as above to sum amounts, and a second to sum the weighted rates from the transactions. I then create a THIRD formula field on the opportunity to divide the two fields. This feels clunky...is there an easier way?
3 is extremely problematic. According to the docs, I cannot do a rollup summary (even a sum) on a formula that changes on the fly - i.e. derived using a NOW() or TODAY() function. My "Accrued Interest to Date" field is derived based on the difference between TODAY() and another date, thus not allowing me to sum it all up. I've seen trigger/apex solutions to this, but I was wondering if there was a point and click method for this? Perhaps a field that updates on a daily basis based on a workflow...therefore not triggering the formula rollup exclusion?