topic Re: Text Box Average calculation in QlikView

Text Box Average calculation

powerqlik — Tue, 14 Feb 2017 23:04:18 GMT

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

aarkay29 — Wed, 15 Feb 2017 05:34:20 GMT

Can you please share the qvw to play with it

Re: Text Box Average calculation

ahaahaaha — Wed, 15 Feb 2017 07:13:23 GMT

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

sdmech81 — Wed, 15 Feb 2017 07:31:36 GMT

yes there is way to do averages in qlikview formula.

But with above explanation,hard to suggest.

Sachin

Re: Text Box Average calculation

powerqlik — Wed, 15 Feb 2017 18:29:15 GMT

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

sunny_talwar — Thu, 16 Feb 2017 09:32:00 GMT

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

powerqlik — Thu, 16 Feb 2017 17:58:42 GMT

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