topic Expression using avg of current selection in QlikView

Expression using avg of current selection

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a real-estate dataframe with several features( square_feet, Number_of_rooms,Number_of_garages,Sale_Price...) and I would like to compare the Saleprice to the average saleprice of similar houses.

That is, select a range of square_feet, Number_of_rooms, Number_of_garages and evaluate :

x = 100*(Sale_Price-Avg(Sale_Price[selection])/(Avg(Sale_Price[selection])))

I would like to display this value in a table with the other features displayed as well.

For now I managed to evaluate my expression with the global average, independant of the selection, and can't figure out what to change.

Re: Expression using avg of current selection

sunny_talwar — Wed, 19 Oct 2016 19:31:43 GMT

Try this may be:

100 * (Avg({1}Sale_Price)-Avg({$} Sale_Price))/Avg({$} Sale_Price)

Where Avg({1}Sale_Price) won't change based on selection, but Avg({$} Sale_Price) will.

Note: You can use Avg(Sale_Price) in place of Avg({$} Sale_price) because when nothing is specified, the default is $

Re: Expression using avg of current selection

swuehl — Wed, 19 Oct 2016 19:54:41 GMT

The context you are trying to create your expression for is important.

If you want to use the expression in a chart with Houses as dimension (and potentially your other features like square feet), you would probably need to use the TOTAL qualifier (or a TOTAL qualifier with field list) to disregard the dimensions:

= 100*(Only( Sale_Price)-Avg(TOTAL Sale_Price) )/(Avg(TOTAL Sale_Price)

If this doesn't help, then it might be helpful if you can post a small sample QVW or some sample records and your expected result.

The Aggregation Scope

Re: Expression using avg of current selection

Thu, 20 Oct 2016 06:21:18 GMT

thank you, the TOTAL do the trick.