topic Re: More Weighted Averages in QlikView

More Weighted Averages

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: More Weighted Averages

sunny_talwar — Thu, 17 Aug 2017 14:55:37 GMT

Try this

RangeSum(

(Column1 expression*0.2),

(Column2 expression*0.6),

(Column3 expression*0.2)

)

Re: More Weighted Averages

Anonymous — Mon, 21 Aug 2017 14:56:23 GMT

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

Re: More Weighted Averages

sunny_talwar — Mon, 21 Aug 2017 14:58:43 GMT

For missing you can do this

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