topic Calculating Mean in QlikView

Calculating Mean

Wed, 25 May 2011 14:28:47 GMT

Hi,

Can somebody please help

YearMonth	Population	CM Tickets	Incident Rate
	2136	533	6.03	Average???
11-03	1050	286	3.27	3.02
11-04	1086	250	2.76	3.02

I have the following table in Qlikview Which calculates the inciden rate based on diving (CM Ticket/Population)*(12 for yearly figure.

I want to be able to have the average showing )which you can see should be 3.02). I can then change the straight table to a chart. I don't want to use the trendline feature here as I plan to calculate upper and lower control limits based on the average.

Please help??

Dave

Re: Calculating Mean

Wed, 25 May 2011 14:57:45 GMT

Dave, Not sure i have understood your question. there are functions like Dimensionality(), SecondaryDimensionality().

Check these functions, may help you.

check the attached application also.

- Sridhar

Re: Calculating Mean

Wed, 25 May 2011 15:08:41 GMT

Sridhar,

Thank you

Not quite what I want, I need the average (3.02) showing on both months as in the gaph above this will enable me to have an average line in a graph.

Once the average is there I can do some other calculations based on this. Been playing around with variables but can't get it to work.

Calculating Mean

pat_agen — Wed, 25 May 2011 15:13:22 GMT

hi,

if you have a straight table with dimension "YearMonth" and expressions like this :

1. sum(Population)

2. sum(cm)

3. (sum(cm)/sum(population))*12 // gives incident rate

4. (sum(total cm)/sum(total population))*12 // gives overall average incident rate

will you get what you want?

Calculating Mean

Wed, 25 May 2011 15:30:19 GMT

Pat,

Thank you.

Again does not quite work, I am expecting to get the values 3.01 repeated in Year month 11-03 and 11-04.

Re: Calculating Mean

pat_agen — Wed, 25 May 2011 15:37:21 GMT

hi,

this is what I got applying my solution exactly as stated: one dimension, 4 expressions in a straight table.

the overall average repeats on each line

Re: Calculating Mean

Wed, 25 May 2011 15:42:46 GMT

Pat,

Any chance you could send me that solution to david.haycock@uk.talaris.com

I am on version 9, still not sure at what point to use the aggr function , this was the answer I was expecting.

Thank you for your help

Dave

Re: Calculating Mean

pat_agen — Wed, 25 May 2011 15:51:21 GMT

here it is - hopefully it was what you are looking for.

if it's ok verify the question as answered

Re: Calculating Mean

Wed, 25 May 2011 15:55:24 GMT

Pat,

There is no table in the view, just blank!

Dave

Re: Calculating Mean

pat_agen — Wed, 25 May 2011 16:04:05 GMT

my mistake.

here it is again plus your corrected version from your 2nd post.

Re: Calculating Mean

Thu, 26 May 2011 09:13:36 GMT

Pat,

Had to tweak it slightly but works a treat. This total function will enable me to do a few reports