Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
powerqlik
Contributor III
Contributor III

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

1 Solution

Accepted Solutions
sunny_talwar

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'

View solution in original post

6 Replies
aarkay29
Specialist
Specialist

Can you please share the qvw to play with it

ahaahaaha
Partner - Master
Partner - Master

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
Specialist
Specialist

yes there is way to do averages in qlikview formula.

But with above explanation,hard to suggest.

Sachin

powerqlik
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

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