Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

powerqlik
New Contributor II

Script issue

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

Tags (2)
1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Script issue

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

12 Replies
vishsaggi
Esteemed Contributor III

Re: Script issue

Did not this work for you?

Here: Scripting error

powerqlik
New Contributor II

Re: Script issue

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
Esteemed Contributor III

Re: Script issue

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
New Contributor II

Re: Script issue

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
Esteemed Contributor III

Re: Script issue

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

powerqlik
New Contributor II

Re: Script issue

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. 

Capture.JPG

vishsaggi
Esteemed Contributor III

Re: Script issue

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

powerqlik
New Contributor II

Re: Script issue

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
Esteemed Contributor III

Re: Script issue

Did you try using

Num(

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

         , '#,#00.0%'

        )

Community Browser