InputAvg function and weighted average

Tue, 01 Sep 2009 08:55:13 GMT

Hi,

Need help with InputAvg feature, please. I need to calculate ASP (Average Selling Price). It is calculated as SUM(GROSS REVENUE)/SUM(Volume). I want to use InputAvg(budget_ASP) function, to do "what-if" analysis by typing "what-if" ASPs into this field. It works fine when I stay at the lowest level of granularity but as soon as I start rolling up numbers, my total is just a simple average but I need a weighted avg by volume. See attached two tables.

My work-around solution is to have two tables: one with the ASP as input field and Gross Revenue calculated (because inputsum() works fine, right) and the second, Gross Revenue as input field and ASP calculated ([Gross Revenue]/[Volume]). I go to the lowest granularity and make change to ASP. After that, I take GR and type it into second table. In this case, my second table has correct ASP at the total/aggregated level.

Is there a way to calculated weighted average for inputavg() function or some other workaround? Otherwise, it is too manual. I want to "what-if" with not only ASP but AUC, AUP and so on.

Note: The second table has a desired ASP total, which is 113.22

Input ASP
Capacity	640	880	1000	Total
Net Volume	16,000	4,000	4,000	24,000
Gross Revenue	$1,515,200	$560,000	$642,000	$2,717,200
ASP	$94.70	$140.00	$160.50	$131.73


Input GR
Capacity	640	880	1000	Total
Net Volume	16,000	4,000	4,000	24,000
Gross Revenue	$1,515,200	$560,000	$642,000	$2,717,200
ASP	$94.70	$140.00	$160.50	$113.22

Re: InputAvg function and weighted average

Fri, 12 Dec 2014 10:03:48 GMT

Hi benkes1,
I have the same problem.
Do you think InputAvg has some special syntax to solve the problem?
Some distribution formula with a weighted measure?

topic InputAvg function and weighted average in QlikView

InputAvg function and weighted average

Re: InputAvg function and weighted average