Script issue

[powerqlik]

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

[vishsaggi]

Did not this work for you?

Here: Scripting error

[powerqlik]

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!

[vishsaggi · Accepted solution]

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

[powerqlik]

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

[vishsaggi]

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

[powerqlik]

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. 

[vishsaggi]

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

[powerqlik]

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.

[vishsaggi]

Did you try using

Num(

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

         , '#,#00.0%'

        )