Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
powerqlik
New Contributor III

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

View solution in original post

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

View solution in original post