topic Re: Calculating overall averages in QlikView

Calculating overall averages

Fri, 12 Oct 2012 09:16:02 GMT

Hi Everyone,

Suppose I have the following dataset:

Practice Patients

A 1000

B 2000

C 6000

What's the formula I need to be able to refer to the OVERALL average for the entire dataset?

I plan to use this to format the cell backgrounds accordingly so that practices A and B would be green as they are below the 3000 average and practice C would be red as they are above the overall 3000 average.

I know how to do the formatting bit, just can't get the equation right to calculate the overall average.

Many thanks,

Robin

Re: Calculating overall averages

Anonymous — Fri, 12 Oct 2012 09:28:28 GMT

avg({1} Patients)

This should work I think

Re: Calculating overall averages

Fri, 12 Oct 2012 09:49:49 GMT

Thanks Johannes but unfortunately this hasn't worked.

Re: Calculating overall averages

jonathandienst — Fri, 12 Oct 2012 09:59:43 GMT

Robin

In the absence of information why Johannes' selection does not work or details of your data structure, I guess that you need something like

=Avg({1} Aggr(Count(Patients), Practice))

Hope that helps

Jonathan

Re: Calculating overall averages

Fri, 12 Oct 2012 10:07:47 GMT

Thanks again, Jonathan but, this hasn't worked either.
Assume the data is exactly as in the original post and I've formatted as follows:

if(PATIENTS > Avg({1} Aggr(Count(Patients), Practice)),RGB(255,0,0),RGB

(0,255,0))

But, all the cells show green.

Hope this helps.

Re: Calculating overall averages

jonathandienst — Fri, 12 Oct 2012 10:54:47 GMT

Firstly - QV field names are case sensitive, so should the field be PATIENTS or Patients? This expression will fail because one of the two sides of the compare will be an error and treated as null.

Secondly - are you doing this in a straight/pivot table or chart? If so, what dimensions are you using? This may mean that PATIENTS has no dtisinct value, but you are comparing a field value with a count of fields.

If you say that the data structure is like the table in the initial post, then the first expression is certainly correct, but I suspect the problem is in the comparison expression - but I am just guessing.

If you post your application or a sample of it, then someone may be able to assist without wild guesses.

Regards

Jonathan

Re: Calculating overall averages

johnw — Fri, 12 Oct 2012 23:33:40 GMT

Perhaps this:

avg({1} total Patients)

But yeah, we're kind of shooting in the dark. As Jonathan suggests, if you really want an answer, post a sample application. This would be trivial to solve if we had the right information.