topic Re: weighted average with set analysis/if statements in QlikView

weighted average with set analysis/if statements

sarahshong — Wed, 25 Nov 2020 16:16:04 GMT

I want to calculate a monthly weighted average using a price field called CYCLE_AVG and volume field called Volume. However, both fields have conditions. For prices, I need only prices each day that are for Spot/spot and for volumes I only want daily Bulk volumes.

I have tried the formula below in a pivot table but it says the expression has an error. Also do I need to aggregate the total volumes by the monthly dimension to get total volumes for the month?

sum(Avg({$<GRIDPT_LABEL_TXT={'Spot/spot'}>} CYCLE_AVG)*Sum({$<[Bulk Rack]={'Bulk'}>} Volume))/Sum({$<[Bulk Rack]={'Bulk'}>} Volume)

Re: weighted average with set analysis/if statements

edwin — Mon, 28 Sep 2020 17:54:42 GMT

Hi just looking at the syntax, the expression appears to be correct. make sure all fields are correct. If the expression is correct, you dont need to AGGR by month, jut add month as the dimension and this as the expression - then it means it is aggregated by month.
the expression does not give anyone unfamiliar with your data model enough information to answer your question so a lot of assumptions have to be made. if this does not help maybe providing more info would help someone else help you.

Re: weighted average with set analysis/if statements

Kushal_Chawda — Mon, 28 Sep 2020 20:54:35 GMT

@sarahshong you may need to use aggr function here to work your expression as expected. Try below

sum(aggr((Avg({$<GRIDPT_LABEL_TXT={'Spot/spot'}>} CYCLE_AVG)*Sum({$<[Bulk Rack]={'Bulk'}>} Volume))/Sum({$<[Bulk Rack]={'Bulk'}>} Volume), Month))

If you have more dimension is pivot table you may need to add that as well in your aggr function

or you can simply try removing outer sum function as below

(Avg({$<GRIDPT_LABEL_TXT={'Spot/spot'}>} CYCLE_AVG)*Sum({$<[Bulk Rack]={'Bulk'}>} Volume))/Sum({$<[Bulk Rack]={'Bulk'}>} Volume)