Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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))
Hi,
one solution might be:
=Sum(Aggr([sales quantity]/Sum(TOTAL [sales quantity])*[product time], product))
hope this helps
regards
Marco
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))
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
Thanks alot for your input guys - it was very helpful.
Kind regards
Michael
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
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.
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
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))
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