Text Box Average calculation

[powerqlik]

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%')

[aarkay29]

Can you please share the qvw to play with it

[ahaahaaha]

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

[sdmech81]

yes there is way to do averages in qlikview formula.

But with above explanation,hard to suggest.

Sachin

[powerqlik]

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'

[sunny_talwar · Accepted solution]

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'

[powerqlik]

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