topic Re: Weighted average in QlikView

Weighted average

dseelam — Tue, 17 Apr 2018 14:03:02 GMT

Hello Folks,

How to calculate weighted avg total for below statement for a straight table?

if(isnull(OldPrize),(sum(GROSS_SALES)/sum(SHIPPED_QUANTITY)),OldPrize)

Re: Weighted average

sunny_talwar — Tue, 17 Apr 2018 14:06:19 GMT

I don't think we have enough information to provide any guidance... do you mind expanding and sharing a sample?

Re: Weighted average

vishsaggi — Tue, 17 Apr 2018 14:07:00 GMT

What is the issue are your %ge not correct? Try this?

if(isnull(OldPrize), sum(GROSS_SALES)/sum(TOTAL SHIPPED_QUANTITY)),OldPrize)

Re: Weighted average

dseelam — Tue, 17 Apr 2018 14:13:49 GMT

Sunny,

I am trying to get Old price in a column and if its null trying to get new price which is sum(GROSS_SALES)/sum(SHIPPED_QUANTITY expression is working great but when it comes to total its calculating wrong - would like to see the weighted average instead of normal avg of the column

Re: Weighted average

dseelam — Tue, 17 Apr 2018 14:18:28 GMT

Hey Vish,

if(isnull(OldPrize), sum(GROSS_SALES)/sum(TOTAL SHIPPED_QUANTITY)),OldPrize)

This part seems incorrect trying to get totals as weighted average instead of normal avg

Re: Weighted average

sunny_talwar — Tue, 17 Apr 2018 14:21:42 GMT

What are your chart dimensions? This isn't what you want?

Avg(Aggr(if(isnull(OldPrize),(sum(GROSS_SALES)/sum(SHIPPED_QUANTITY)),OldPrize), YourChartDimensions))

What is the weight based on?

Re: Weighted average

dseelam — Tue, 17 Apr 2018 14:30:47 GMT

Hey Sunny,

My chart dimension is Item number but expression you sent me was giving normal avg of the column which is the same totals when I use default expression total

Re: Weighted average

dseelam — Tue, 17 Apr 2018 14:35:08 GMT

Sunny, attached an excel with an example column M is what am having using avg expression total, What I need was column 'O'

Re: Weighted average

sunny_talwar — Tue, 17 Apr 2018 14:41:23 GMT

May be try this

Sum(Aggr(if(isnull(OldPrize),(Sum(GROSS_SALES)/Sum(SHIPPED_QUANTITY)),OldPrize), License, [Acct #], [Customer Name], Customer))/Sum(Quantity)