topic Re: Complex averaging problem in QlikView

Complex averaging problem

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

Hi,

I'm new to Qlikview and I'm not even sure how to phrase my question. Im looking at simple survey data (pic attached), at the moment for each question I can show a barchart(labelled) with the question answers (represented as numbers) on the x axis and the frequency with which they were chosen on the y axis.

The expression is = Count(DISTINCT users) and it nicely shows the distribution of answers per question.

My problem is this, I am trying to get an 'average' score for each question shown in a gauge (if possible?). In the question below 10 people chose option 1, 5 chose option 2, 2 chose option 3 and 3 chose option 4.

I would like an expression to calculate the the average of the multiplication y axis value by the x axis value for each x value

Just for clarity, in the case below it would be =Avg((10 * 1) + (5 * 2) + (2 * 3) + (3 * 4))

I hope that made sense!

Can anyone point me in the right direction?

Re: Complex averaging problem

sunny_talwar — Wed, 08 Mar 2017 20:14:33 GMT

May be this

Avg(Aggr(Count(DISTINCT users) * number, number))

Sum(Aggr(Count(DISTINCT users) * number, number))/Count(DISTINCT users)

I think 1st expression might not work... but I may be wrong. 1st one should give you 9.5, whereas the second one should give you 1.9

Re: Complex averaging problem

Anonymous — Thu, 09 Mar 2017 08:00:28 GMT

Hi Sunny,

I was looking for the second equation, sorry for not being clear enough but you managed anyway!

Thanks so much

George