Weighted Average Formula in Formatted Report

Tue, 09 Nov 2010 10:45:51 GMT

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!

topic Weighted Average Formula in Formatted Report in QlikView

Weighted Average Formula in Formatted Report