topic Re: Script issue in QlikView

Script issue

powerqlik — Thu, 14 Sep 2017 18:47:07 GMT

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.

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

Re: Script issue

vishsaggi — Thu, 14 Sep 2017 18:53:20 GMT

Did not this work for you?

Here: Scripting error

Re: Script issue

powerqlik — Thu, 14 Sep 2017 18:56:11 GMT

The one you helped me with worked. This one is a little different but I can't determine why it's not displaying accurately as an overall %. Can you please take a look? Thanks!

Re: Script issue

vishsaggi — Thu, 14 Sep 2017 19:03:04 GMT

Try this:

= num(

Avg(Aggr(((

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), [Address Suite])) , '#,#0.0%')

Re: Script issue

powerqlik — Thu, 14 Sep 2017 19:16:57 GMT

That didn't seem to display accurate results either. Maybe if you can help me break it out further that way I can test where it's going wrong. Can you please review my individual formulas and create 3 separate text box objects?

The first one will display the overall average current rent expense. This is what I currently show. Can you please modify if I am incorrectly writing the overall average formula?

=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])),'$#.##')

The second one will display overall average market rent expense. This is what I currently show. Can you please modify if I am incorrectly writing the overall average formula?

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

/if([Unit of Measurement] <> 'SQFT', $(vSQMtoSQFT), 1), [Address Suite])),'$#,##0.00')

And of course, the third one will display the overall average which you created from the above.

= num(

Avg(Aggr(((

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), [Address Suite])) , '#,#0.0%')

Re: Script issue

vishsaggi — Thu, 14 Sep 2017 19:28:53 GMT

Can you please share a sample you are working on so that it would be easy to look into. ? With you expected output.

Re: Script issue

powerqlik — Thu, 14 Sep 2017 19:48:51 GMT

I really wish I can but it belongs to the company and I unfortunately can't upload it. You have been so great and I appreciate your help. My formulas for Current gross (current rent) and market gross (market rent) are displaying accurate results. Some markets (also known as address suite) have multiple locations in the address suite and thats why I am trying to make sure my formulas are correctly depicting an overall average rent expense and an overall average market expense.

Re: Script issue

vishsaggi — Thu, 14 Sep 2017 20:02:24 GMT

So you are not getting the correct Avg from the formula i sent?

Re: Script issue

powerqlik — Thu, 14 Sep 2017 20:23:18 GMT

Doesn't appear so. Your formula displays the correct results for each over/under market %; however, when I don't select a specific address suite for it to get me the overall average of every single address suite, it doesn't display accurate results.

Re: Script issue

vishsaggi — Thu, 14 Sep 2017 20:47:14 GMT

Did you try using

Num(

AVG( Aggr ( YOURWHOLE Expression here, [Address Suite]))

, '#,#00.0%'

)

Re: Script issue

powerqlik — Thu, 14 Sep 2017 21:05:54 GMT

Forgive me but for some reason that shows blank. I must not have entered it accurately

Re: Script issue

vishsaggi — Thu, 14 Sep 2017 21:11:24 GMT

can you paste the script what you are trying?

Re: Script issue

powerqlik — Thu, 14 Sep 2017 21:48:43 GMT

Your initial response was actually correct. I just double checked the data and I agree with the output. Thank you!