Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

InputAvg function and weighted average

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
Capacity6408801000Total
Net Volume16,0004,0004,00024,000
Gross Revenue$1,515,200$560,000$642,000$2,717,200
ASP$94.70$140.00$160.50$131.73
Input GR
Capacity6408801000Total
Net Volume16,0004,0004,00024,000
Gross Revenue$1,515,200$560,000$642,000$2,717,200
ASP$94.70$140.00$160.50$113.22
1 Reply
Not applicable
Author

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?