12 Replies Latest reply: Sep 14, 2017 5:48 PM by ALEN ARUTYUNYAN RSS

    Script issue

    ALEN ARUTYUNYAN

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

        • Re: Script issue
          Vishwarath Nagaraju

          Did not this work for you?

          Here: Scripting error

            • Re: Script issue
              ALEN ARUTYUNYAN

              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
                  Vishwarath Nagaraju

                  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
                      ALEN ARUTYUNYAN

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