Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
powerqlik
New Contributor II

Scripting error

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 20% it displays more like 900%.

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"}, ExpenseMonth = {$(vThisMonth)}>} if(ExpenseYear = $(vThisYear), Amount))*12)/(sum(if(LeaseTypePortfolio = 'Lease', Size))), [Address Suite])))

This formula gives me the average market rate for the current market

(((avg(if([Currency] = 'USD',[New SP - Rental Rates]*1,[New SP - Rental Rates]*$(vUSDCurrencyRate2)))/if([Unit of Measurement] <> 'SQFT', $(vSQMtoSQFT), 1))))-1),'#,##0.0%')

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"}, ExpenseMonth = {$(vThisMonth)}>} if(ExpenseYear = $(vThisYear), Amount))*12)/(sum(if(LeaseTypePortfolio = 'Lease', Size))), [Address Suite])))/

(((avg(if([Currency] = 'USD',[New SP - Rental Rates]*1,[New SP - Rental Rates]*$(vUSDCurrencyRate2)))/if([Unit of Measurement] <> 'SQFT', $(vSQMtoSQFT), 1))))-1),'#,##0.0%')

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Scripting error

I might be wrong. But can you Try this? 

= num(

        Avg(Aggr(

                  (

                   (

                     Avg(Aggr((sum({$< LeaseTypeExpenses = {Lease},

                                  Expense           = {"*"} - {"VAT","Parking","Free Rent"},

                                  ExpenseMonth      = {$(vThisMonth)}

                                >} if(ExpenseYear = $(vThisYear), Amount))*12)

                               /

                         (sum(if(LeaseTypePortfolio = 'Lease', Size))), [Address Suite])))

                         /

                     (((avg(if([Currency] = 'USD',[New SP - Rental Rates]*1,[New SP - Rental Rates]*$(vUSDCurrencyRate2)))

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

2 Replies
vishsaggi
Esteemed Contributor III

Re: Scripting error

Where are you displaying this in a textbox? Can you share a sample to look into?

vishsaggi
Esteemed Contributor III

Re: Scripting error

I might be wrong. But can you Try this? 

= num(

        Avg(Aggr(

                  (

                   (

                     Avg(Aggr((sum({$< LeaseTypeExpenses = {Lease},

                                  Expense           = {"*"} - {"VAT","Parking","Free Rent"},

                                  ExpenseMonth      = {$(vThisMonth)}

                                >} if(ExpenseYear = $(vThisYear), Amount))*12)

                               /

                         (sum(if(LeaseTypePortfolio = 'Lease', Size))), [Address Suite])))

                         /

                     (((avg(if([Currency] = 'USD',[New SP - Rental Rates]*1,[New SP - Rental Rates]*$(vUSDCurrencyRate2)))

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

Community Browser