6 Replies Latest reply: Feb 16, 2017 12:58 PM by ALEN ARUTYUNYAN

# Text Box Average calculation

Hi Guys,

I was wondering if anyone can assist me with a script.  I am not sure what I am doing wrong but what I am trying to accomplish is an overall average over/under calculation of all of my real estate locations.

So for each location, I have current rents (LeaseType) and for each of those locations, I have market rates (New SP - Rental Rates).  The formula below accurately calculates the change between current rent vs market rent in a percentage format for each location I select; however, it does not calculate an average of all locations when no location is selected.

Is there a way to utilize this formula to calculate an average for all the locations in a text box?

Sorry if I have not asked this question in the appropriate location as this is my first time in this forum.

=num(((((sum({\$<LeaseTypeExpenses = {Lease}, Expense = {"*"}-{"VAT","Parking","Free Rent"}, ExpenseMonth = {\$(vThisMonth)}>} if(ExpenseYear = \$(vThisYear), Amount)))/sum(Size))*12))/

(sum([Rental Rates]*[Units per USD])/if([Unit of Measurement] <> 'SQFT', \$(vSQMtoSQFT), 1))-1,'#,##0.00%')

• ###### Re: Text Box Average calculation

Can you please share the qvw to play with it

• ###### Re: Text Box Average calculation

Hi Alen,

It is difficult to give advice without seeing the data. The operator TOTAL after the expression Set Analisys does not solve your problem?

Regards,

Andrey

• ###### Re: Text Box Average calculation

yes there is way to do averages in qlikview formula.

But with above explanation,hard to suggest.

Sachin

• ###### Re: Text Box Average calculation

Hi guys, thanks for the prompt follow up!  Unfortunately, I can't provide the file as it's considered work propitiatory; however, I realize the formula attached is a bit complicated.  Let me try to simplify, first formula below calculates real estate market rates in different locations.  It takes the total of [New SP - Rental Rates] and multiplies it by USD dollar and converts it to Square feet and presents the amount in in USD.  Is there a way I can get an average for multiple locations?  This formula works correctly when I select a specific location (Los Angeles, London, England, New York); however, it does not produce an accurate result when I don't select the radio button for Los Angeles, London, etc) and leave the location blank.

If you were to modify this formula, how can you modify it to include an average? =Avg(aggr(sum?  Would someone please attempt to modify it?

=num(sum([New SP - Rental Rates]*[Units per USD])

/if([Unit of Measurement] <> 'SQFT', \$(vSQMtoSQFT), 1),'\$#,##0.00') &' '&'USD'

• ###### Re: Text Box Average calculation

Can you try this:

=Num(Avg(Aggr(Sum([New SP - Rental Rates] * [Units per USD])/If([Unit of Measurement] <> 'SQFT', \$(vSQMtoSQFT), 1), Location)),'\$#,##0.00') &' '&'USD'

• ###### Re: Text Box Average calculation

Thank you to everyone that helped on this!  You guys are awesome.  Special thanks to Sunny for modifying the script to work!