Weighted Average

[Anonymous]

On the table above I need a weighted average for the Day 01 Weight Column total (Currently 200). To get this the formula needs to multiply the Day 01 weight for each From Group by the Head Placed and then add them all together and divide by the total Head placed. Any help on this will be greatly appreciated.

[Anonymous]

Here is what I'm looking for:

6,018 * 65 = 391,170

6,309 * 69 = 435,321

8,052 * 66 = 531,432

                 1,357,923 / 20,379 = 66.63

[Anonymous]

Ok, so I found a formula for the weighted average and it works if I only have one Group Cd selected, but when I have all of the codes I need it to group just the amounts in each Group Cd separately. Any ideas?

Sum(Total fDay1_wt * Aggr(Sum(head_Start), fDay1_wt)) / Sum (Total Aggr(Sum(Head_Start), fDay1_wt))

[#4]

Perhaps try:

Sum(Total <Group Cd> fDay1_wt * Aggr(Sum(head_Start), fDay1_wt)) / Sum (Total <Group Cd> Aggr(Sum(Head_Start), fDay1_wt))

Sounds like you need the average to take Group Cd into account. By adding Total <Group Cd> it should ignore other dimensions and sum over each Group Cd.

Hope that helps.

Matt

[marcus_sommer]

Maybe this is helpful for you: http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/29/averages

- Marcus

[Anonymous]

Matt,

I think you are right, but the code did not return a result. Any ideas of a way to change formula around to recalculate based on Group Cd?

[#7]

Hi Brad,

That blog post Marcus posted will definitely have some good tips. Otherwise if you could post some sample data that would make it a little easier.

To trouble shoot it what I'd do is break the expression apart, just work with the numerator, for instance, until you get the number you need. Looking at my original suggestion, the Total <Group Cd> in the denominator is likely causing the issue. Maybe we need to wrap the whole expression in another aggr, like:

Sum(

     aggr(

          Sum( fDay1_wt * Aggr(Sum(head_Start), fDay1_wt))

          / Sum (Aggr(Sum(Head_Start), fDay1_wt))

     ,[Group Cd]

          )

       )

Hope that helps.

matt