Hi All,
I have a requirement to calculate a weighted average on a YTD basis within a formatted report.
Please see below.
I am trying to create a weighted average for the USD/AUD Rate, weighting the Rate with the 'Total Cost (Cash + Non Cash)'. So in essence, relating two lines of the formatted report to each other for a formula.
I have done this in the past with simple divisions and multiplications by flagging the amount and using a SET.
I could create a formula flag and change the formula to as follows
If(FLFormulaType = 'WA',
Sum ({$<FLExpNum = {'1'}>} TranTotal) /
Sum ({$<FLExpNum = {'2'}>} TranTotal)
But in order to create a weighted average, you need to encase the multiplication formula with Sum(). The reason for this is it isn't the TOTAL FLExpNum1 * TOTAL ExpNum2, it is ExpNum1 * ExpNum2 on a month to month basis. I.E.
Sum(TranTotal1 * TranTotal2).
The difficulty with this is they can not be flagged in this manner, and i am dealing with a transactional table which hasn't been grouped into months making it even more difficult.
Can I use Exp() or agg() here?
I'm really at a loss what to do from here so any help would be great!