Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

More Weighted Averages

Hi guys,

So, I managed to get halfway there with some weighted averages I have been working on.

I have three columns of scores, of which the first and last are worth 20% each of the weighted overall score, and the second column is worth the remaining 60%.

The expression works a dream for the most part. It's as follows:

((Column1 expression*0.2) + (Column2 expression*0.6) + (Column3 expression*0.2))

However, it falls over should any of the columns have a blank field. I've racked my brains how to resolve this without effecting the integrity of the weightings, but to no avail. For instance, if I amend the expression to count Column 3 as worth double if Column 1 is blank, then the expression is going to return a result that would suggest column 3 is almost as important as column 2, which is a fallacy.

Could any of you lovely people advise what I should do to resolve this please?

Many thanks as always,

Gareth

3 Replies
sunny_talwar

Try this

RangeSum(

(Column1 expression*0.2),

(Column2 expression*0.6),

(Column3 expression*0.2)

)

Anonymous
Not applicable
Author

Hi Sunny

Thanks - however this is pretty much what I had unfortunately. It treats the blanks as zero's, which is incorrect because that's then saying a supplier who doesn't have a score against them in column 1 (for whatever reason) is the same as having scored 0% in column 2.

I somehow need to differentiate between this - I figure the weightings might need to change on the occasion of a missing score but really not sure how to go about this.

Thanks,

Gareth

sunny_talwar

For missing you can do this

If(Len(Trim(Column1 expression)) = 0, When Null Action, When Not Null Action)