topic Re: Weighted Average in time spread... in QlikView

Weighted Average in time spread...

Mon, 13 Jun 2016 07:40:14 GMT

Dear users,

I want to show weighted average on a Line Chart. Dimensions are: YearMonth and Category.

Below example regarding one project, there will be many YearMonth records, but Category will be still 1, 2, 3...

At the moment, my weighted average metric is like below, but it calculates me total by number of months. Is it possible to show me aggregated weighted average for each YearMont dimension? This will allow to show me weighted averages on linear chart, so there will be possibility to track weighted average changes in time.

Thank you in advance.

//Weighted average metric:
sum (
    aggr(
        (sum(volume) / sum(total volume))*
          sum(nettprice), category, YearMonth)    )

YearMonth	Category	NettPrice	Weighted Avg	Volume	Desired goal of showing weithted avg on line chart
	Category1		216	1 263,34
2016-06	Category1	216,00	30,86	180,48	216,00
2016-07	Category1	216,00	30,86	180,48	216,00
2016-08	Category1	216,00	30,86	180,48	216,00
2016-09	Category1	216,00	30,86	180,48	216,00
2016-10	Category1	216,00	30,86	180,48	216,00
2016-11	Category1	216,00	30,86	180,48	216,00
2016-12	Category1	216,00	30,86	180,48	216,00
	Category2		115,2	134,55
2016-06	Category2	115,20	16,46	19,22	115,20
2016-07	Category2	115,20	16,46	19,22	115,20
2016-08	Category2	115,20	16,46	19,22	115,20
2016-09	Category2	115,20	16,46	19,22	115,20
2016-10	Category2	115,20	16,46	19,22	115,20
2016-11	Category2	115,20	16,46	19,22	115,20
2016-12	Category2	115,20	16,46	19,22	115,20

Re: Weighted Average in time spread...

marcus_sommer — Mon, 13 Jun 2016 12:22:45 GMT

It's not quite clear for me how do you want to calculate the average but maybe you need a further aggr() for it, maybe something like this:

avg(aggr(

sum (aggr(

(sum(volume) / sum(total volume))* sum(nettprice), category)),

YearMonth))

Also this might be helpful for you: Average – Which average?

- Marcus

Re: Weighted Average in time spread...

sunny_talwar — Mon, 13 Jun 2016 12:31:26 GMT

Not sure, but may be this:

Sum(TOTAL <Category> Aggr( (Sum(volume)/Sum(TOTAL volume)) * Sum(nettprice), category, YearMonth))

Re: Weighted Average in time spread...

Mon, 13 Jun 2016 12:55:23 GMT

Hi Marcus,

thank you but there is no information regarding weighted average.

Best,

Krzysztof

Re: Weighted Average in time spread...

Mon, 13 Jun 2016 13:02:09 GMT

Hi Sunny,

thank you. I tried this. Using TOTAL returns me the same number for each month.

But I know that if I have e.g. 40 records in each category in structure:

YearMonth, Category, Volume, NetPrice

I should have receive different weighted average for each sum:

YearMonth Category weighted avg

2016-06 Category1 199

2016-07 Category1 195

2016-06 Category2 205

2016-07 Category2 201

Best,

Krzysztof

Re: Weighted Average in time spread...

sunny_talwar — Mon, 13 Jun 2016 13:44:24 GMT

Hi Krzysztof -

I am not sure how you are arriving at the numbers you have listed above. Would it be possible to share some raw data with the expected output?

Best,

Sunny

Re: Weighted Average in time spread...

Mon, 13 Jun 2016 18:42:38 GMT

Hi Sunny,

attached you will find dummy data. Thank you. As you can see weighted average is correct... but when it is sum up.

Best,
Krzysztof

Re: Weighted Average in time spread...

sunny_talwar — Mon, 13 Jun 2016 19:11:15 GMT

I guess I am still having trouble understanding what your desired goal is?

Is this the correct desired goal you are looking to achieve? This seems to be repeating the weighted average number for each month for a particular category.

I think your sample is good, I just need to know what the expected output needs to be in order to provide any help from here.

Best,

Sunny

Re: Weighted Average in time spread...

sunny_talwar — Mon, 13 Jun 2016 19:13:29 GMT

Is this correct?

What exactly do you want to see differently here?

Re: Weighted Average in time spread...

Tue, 14 Jun 2016 05:24:13 GMT

No, this is incorrect. Look, weighted average should be counted: price1*sales1+price2*sales2... / Total sum(sales) for category and month, thus for category1 and 2016-06 should be: 200*195+230*340+245*150 / 195+340+150 = 224,74.

So, standard average: 225,00; weighted average: 224,74.

Re: Weighted Average in time spread...

sunny_talwar — Tue, 14 Jun 2016 09:24:12 GMT

Try this:

Sum(Sale * NetPrice)/Sum(Sale)

Re: Weighted Average in time spread...

Wed, 15 Jun 2016 13:05:10 GMT

Hello Sunny,

thank you, but in the meantime I used expression but with PriceVolume calculation in the script.

In fact, whole process is far more complicated in my application than in my example, but it worked.

Result is the same, so we may close this topic. Thank you.

Best,
K.