Weighted average

[Anonymous]

Hi,

I have this challenge where I would like to calculate the average of product time across products (a, b, c).

The simple avg function gives me the average (10 + 100 + 300) / 3  =  137.

But i want the weighted average where sales quantity is taken into account: (10/90)*10 + (30/90)*100 + (50/90)*300 = 201.

How do i write an expression for this?`

/ Michael

year	month	product	sales quantity	product time
2016	august	a	10	10
2016	august	b	30	100
2016	august	c	50	300
			90

[MarcoWedel]

Hi,

one solution might be:

=Sum(Aggr([sales quantity]/Sum(TOTAL [sales quantity])*[product time], product))

hope this helps

regards

Marco

[sunny_talwar · Accepted solution]

Or may be this if this has to be done for multiple periods

Sum(Aggr(Sum([sales quantity])/Sum(TOTAL <year, month> [sales quantity]) * Sum([product time]), year, month, product))

[effinty2112]

Hi Michael,

          This is how I interpret your requirements:

month	product	=Sum([sales quantity]*[product time])/Sum(TOTAL [sales quantity])
		201.11111111111
august	a	1.1111111111111
august	b	33.333333333333
august	c	166.66666666667

Kind regards

Andrew

[Anonymous]

Thanks alot for your input guys - it was very helpful.

Kind regards

Michael

[Anonymous]

Hi Sunny,

Thanks for your answer.

I have now the correct result for each month, but the wrong value for the year in total. I'm using a straight table with the month as the dimension.

The year total is just the average across months (238), and not the weighted average (246) where sales quantity is taken into account. So it is kind of the same problem as before. Can I somehow get the correct weighted average in the total row in a straight table.

	jan	feb	mar	apr	may	jun	jul	aug	sep	Total	Total average should be weighted average
Average product time	150	170	180	190	200	300	350	201	400	238	246
Sales quantity	80	65	70	75	85	90	90	90	100	745

Kind regards,

Michael

[sunny_talwar]

How exactly are you coming up with 246? I don't have the raw data to determine, but can you explain what the logic behind this calculations needs to be? Also, if possible can you provide a sample? It would be much easier to resolve this if we can take a look at it in a sample qvw file.

[Anonymous]

Hi, Sunny

!

Here is a sample of my problem. These are some new random numbers, so 246 is not the result anymore.

The general issue is, that I want my total to be the "year-to-date" average (201,64) and not the average across the averages in each month (199). Can that be achieved?

I guess i could put a text box above the chart's total cell, but I was hoping for a better solution.

Also, I don't think that an expression for each month and one for the total is an option either, because I need several other rows in the chart.

I hope this makes any sense to you

[haribabugv]

Hello Sunny

How to use the below weighted average expression between two time periods?

My below expression is for a text box and i am getting error

if(date(Date) >= '$(vStart)' and date(Date) <= '$(vEnd)',Sum(Aggr(Sum(Actual * Quantity)/Sum(Actual),Class))))

Sunny Talwar &lt;span class=&quot;icon-status-icon icon-mvp&quot; title=&quot;Mvp&quot;&gt;&lt;/span&gt; wrote:

Or may be this if this has to be done for multiple periods

Sum(Aggr(Sum([sales quantity])/Sum(TOTAL <year, month> [sales quantity]) * Sum([product time]), year, month, product))

stalwar1‌

[sudhakar_budde]

Hi Michael,

I am facing the same challenge where the weighted avg don't role up as weighted avg from month to year!

Did you find the solution at the end?

Can you please put me in a right direction?

Thanks

SB