topic Re: Weighted average in QlikView

Weighted average

Anonymous — Fri, 09 Sep 2016 09:03:13 GMT

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

Re: Weighted average

MarcoWedel — Fri, 09 Sep 2016 21:53:50 GMT

Hi,

one solution might be:

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

hope this helps

regards

Marco

Re: Weighted average

sunny_talwar — Fri, 09 Sep 2016 22:07:02 GMT

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

Re: Weighted average

effinty2112 — Sat, 10 Sep 2016 10:47:02 GMT

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

Re: Weighted average

Anonymous — Wed, 14 Sep 2016 10:02:39 GMT

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

Kind regards

Michael

Re: Weighted average

Anonymous — Wed, 14 Sep 2016 15:05:13 GMT

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

Re: Weighted average

sunny_talwar — Thu, 15 Sep 2016 01:11:52 GMT

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.

Re: Weighted average

Anonymous — Sun, 18 Sep 2016 20:15:43 GMT

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

Re: Weighted average

haribabugv — Thu, 20 Sep 2018 07:07:01 GMT

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 <span class="icon-status-icon icon-mvp" title="Mvp"></span> 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‌

Re: Weighted average

sudhakar_budde — Wed, 26 Sep 2018 12:48:22 GMT

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

Re: Weighted average

sudhakar_budde — Wed, 26 Sep 2018 12:55:27 GMT

Hi Marco,

I am facing the same challenge. But for the year 2016 I wanted to see the 201 as Michael asked on the totals!

Any ideas please?

Thanks

Re: Weighted average

Rich5678 — Tue, 30 Aug 2022 19:25:12 GMT

Can someone please respond to MichaelWad's question..

How to get the Weighted Average in Total Row of Simple Table?