Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
pam1990
Contributor III
Contributor III

How to have weighted average work with filter selections

Hello,

I have the following measure:

=Sum((Principal/TotalGrossSquareFootage)*Principal)/Sum(Principal)

 

The (Principal/TotalGrossSquareFootage) portion is the loan per sq ft, which I want to weight.

At the record level, Sum((Principal/TotalGrossSquareFootage)*Principal)/Sum(Principal) works as expected, but the weighting amount at the summary level is incorrect and when I filter the summary  is incorrect.

In addition, I do not want to include zeros in my weighting. I did not address that above because I do not have the weighting working correctly.

This is a filter for one loan:

The weighted summary should be 60.19, not 180.57.

pam1990_0-1621551699467.png

And the one below should be about 106, not 481.41.

pam1990_1-1621551800738.png

I cannot figure out what I am doing wrong. 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
pam1990
Contributor III
Contributor III
Author

I solved my issue. I replaced Principal in =Sum((Principal/TotalGrossSquareFootage)*Principal)/Sum(Principal)

With Principal*(LeasedPropertySqFt/
if(Alt(TotalGrossSquareFootage,0)=0,GrossSquareFootage,TotalGrossSquareFootage))

This did the trick.

View solution in original post

1 Reply
pam1990
Contributor III
Contributor III
Author

I solved my issue. I replaced Principal in =Sum((Principal/TotalGrossSquareFootage)*Principal)/Sum(Principal)

With Principal*(LeasedPropertySqFt/
if(Alt(TotalGrossSquareFootage,0)=0,GrossSquareFootage,TotalGrossSquareFootage))

This did the trick.