topic Re: Average with negative values in QlikView

Average with negative values

17George — Tue, 16 Aug 2022 10:23:20 GMT

Hello,
On the left side the scores with some negative values and on the right side
the modified results if I use fabs( sum(score)/count(name)) or RangeAvg(fabs(score)).

How can I keep the scores unchanged and also show the average value of them in my expression?

Re: Average with negative values

marcus_sommer — Tue, 16 Aug 2022 11:19:52 GMT

You don't mentioned the place and context in which the values/results should be displayed. Therefore various approaches are thinkable, for example:

adding an extra field with positive/negative information
using dual() values, like: dual(value, fabs(value))
querying the dimensionality in a pivot, like if(dimensionality() = 0, fabs(value), value)

- Marcus

Re: Average with negative values

PrashantSangle — Tue, 16 Aug 2022 11:29:03 GMT

Use fabs() inside sum()

sum(fabs(score))/count(name)

Regards,

Re: Average with negative values

17George — Tue, 16 Aug 2022 12:44:39 GMT

Thanks @marcus,
both suggestions, dual( ) & dimensionality() worked as desired in my table.

Is there a possibility to adjust the results according to the weighted factor of +/- signs?
For example: avg (-3, -7) = 5 and sum(-3,-7) = -10.
Since the negative sign is overweighing, -5 might be adopted as "adjusted" average.

Re: Average with negative values

marcus_sommer — Tue, 16 Aug 2022 13:05:47 GMT

I'm not sure what you mean with an adjusted average but both in the origin question mentioned methods may be adaptable for it - means: sum() / count() and/or rangeavg() - directly or combined with an aggr(), maybe like:

avg(aggr(sum() / count() , Dim1, Dim2))

rangeavg(aggr(only(Value), Value))

- Marcus