topic Re: Weighted Average as KPI Stat in QlikView

Weighted Average as KPI Stat

Anonymous — Thu, 17 Jul 2014 10:16:45 GMT

All,

I am looking for some help on weighted averages. I have looked through the other examples on this site but cannot find one to match my query.

I have a list of companies with a market value held, target price and current price. What I want to do is to be able to show a one item key performance indicator that shows the weighted average total return for all stocks selected by the user.

I have attached the sample data in excel as well as my attempt to weighted average in the attached QVW.

Sample excel table shown below - the columns 'DB field' are in the excel file and are known. The 'calculation fields' are used to calculate the weighted average return.

DB FIELDS				CALCULATION FIELDS
DESCRIPTION	AUM	Target	Current	Expd TR	Weight	Weighted TR
COMPANY A	392,674,467	18.4	16.37	12.4%	60.3%	7.5%
COMPANY B	258,514,789	2.2	1.82	20.9%	39.7%	8.3%
W AVG TR	15.8%

I have been able to get to the point of calculating the market value weighted total return in a pivot table (listed as 'W AVG EXPD RTN' in the QVW) but I cannot work out how to

a) Sum this value within the pivot (partial sum gives a blank)

b) have the calculation bundled up into a separate field (e.g. Weighted Average Total Return text box in the attached QVW).

Really would appreciate help on this one - I have been struggling with this one for some time.

Re: Weighted Average as KPI Stat

Thu, 17 Jul 2014 10:28:41 GMT

the fastest way is change the control from Pivot table to straight table and use "Sum" as the total Mode for that field.....

Re: Weighted Average as KPI Stat

marcus_malinow — Thu, 17 Jul 2014 10:32:27 GMT

If you simply want to Sum your W AVG EXPT RTN, try this

sum(aggr(Sum((TARGET - CURRENT)/CURRENT)* SUM(MARKET_VALUE)/SUM(TOTAL MARKET_VALUE)), DESCRIPTION))

Re: Weighted Average as KPI Stat

Anonymous — Thu, 17 Jul 2014 10:34:22 GMT

Thanks - but even as straight table I still get a '-' in the total column. When you say 'use sum as total mode' - what does this mean exactly, I cannot see this as an option, or do you mean creation of a custom label that is SUM(w avg column)

Also this would not answer my key issue to have a separate cell that consolidates the results

Re: Weighted Average as KPI Stat

Anonymous — Thu, 17 Jul 2014 10:38:28 GMT

Boom! That works a treat!! I cant thank you enough

Clearly I need to brush up on my understanding of the aggr function

Re: Weighted Average as KPI Stat

Anonymous — Thu, 17 Jul 2014 15:18:12 GMT

Marcus - could I ask for your assistance again here: as I have come to implement this fully I have hit a snag which seems to be breaking the formula, I cannot see why.

I am using the following formula in a table to get the correct weighted total return. The difference here is the change to the denominator - I needed to exclude the market value of holdings where there is no target price as this would otherwise skew the number when aggregated. The result is correct in the column is it in but as before there is no sum (as no aggr is being used at this stage presumably).

((Target - Current)/Current)*(SUM(MKT_VAL)/(SUM( TOTAL {$<Target = {'*'}-{''}>} MKT_VAL))

Implementing the same formula that worked earlier combined with the above I am getting crazy results

NUM(sum(aggr(Sum((Target - Current)/Current)*(SUM(MKT_VAL)/SUM( TOTAL {$<Target = {'*'}-{''}>} MKT_VAL)), DESCRIPTION)),'#.#%')

DESCRIPTION	TARGET	PRICE	TOTAL RETURN	MKT VALUE	% All	Weighted Avg	Aggr Formula	Multiple Diff
				1,722,421,335			-27.86%
COMPANY A	120.00	108.15	10.957%	1,181,628,840	68.6%	7.5168%	60.13%	8.00
COMPANY B	18.40	16.37	12.401%	392,674,467	22.8%	2.8271%	8.48%	3.00
COMPANY C	96.00	483.20	-80.132%	148,118,029	8.6%	-6.8909%	-96.47%	14.00

Strangely, the 'wrong' result (total of -27%) is based on exact multiples of the correct weighted average on a line by line basis.

Can you see what I am doing wrong here?

Re: Weighted Average as KPI Stat

marcus_malinow — Fri, 18 Jul 2014 08:41:36 GMT

Hi Ben,

can't see anything obviously wrong.

Perhaps adding a flag in the load to indicate a NULL Target value might help.

Alternatively if you upload another copy of your QVW someone might be able to help.

Marcus