Hi Guys, I am trying to determine what's wrong with my script below and any help would be great. Thanks! The script below gives you overall percentage change based on "Address Suite"; however, for some reason, it's it only displays accurate results when I select individual "Address Suites" in my dashboard. When I clear the dashboard, it doesn't populate an accurate average overall % change of lets say 12% it displays more like 26%. Please note this formula is in a text box object.
To clarify, this formula gives me the average rent expense for the current market.
=num((Avg(Aggr(sum({$<LeaseTypeExpenses = {Lease}, Expense = {"*"}-{"VAT","Parking","Free Rent","Other"}>} if(left(Period,4) = $(vThisYear), Amount))/(sum(if(LeaseTypePortfolio = 'Lease', Size))), [Address Suite]))
This formula gives me the average market rate for the current market
num(Avg(Aggr(sum([New SP - Rental Rates]*[Units per USD])
/if([Unit of Measurement] <> 'SQFT', $(vSQMtoSQFT), 1), [Address Suite])),'$#,##0.00'))
When I divide the two amounts and subtract by 1 to get the percentage change, it displays accurate results based on each market I select; however, I am trying to determine how to give me an overall average % change if I don't select a specific "Address Suite"
=num((Avg(Aggr(sum({$<LeaseTypeExpenses = {Lease}, Expense = {"*"}-{"VAT","Parking","Free Rent","Other"}>} if(left(Period,4) = $(vThisYear), Amount))/(sum(if(LeaseTypePortfolio = 'Lease', Size))), [Address Suite]))/num(Avg(Aggr(sum([New SP - Rental Rates]*[Units per USD]) /if([Unit of Measurement] <> 'SQFT', $(vSQMtoSQFT), 1), [Address Suite])),'$#,##0.00'))-1,'#,#0.%')
