topic Re: Weighted Average in QlikView

Weighted Average

Anonymous — Thu, 14 Aug 2014 14:05:41 GMT

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.

Re: Weighted Average

Anonymous — Thu, 14 Aug 2014 14:40:23 GMT

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

Re: Weighted Average

Anonymous — Thu, 14 Aug 2014 15:10:24 GMT

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))

Re: Weighted Average

Thu, 14 Aug 2014 18:20:21 GMT

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

Re: Weighted Average

marcus_sommer — Thu, 14 Aug 2014 18:47:05 GMT

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

- Marcus

Re: Weighted Average

Anonymous — Thu, 14 Aug 2014 20:13:19 GMT

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?

Re: Weighted Average

Thu, 14 Aug 2014 23:11:29 GMT

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