topic Re: How can I calculate mean, standard deviation etc. over groups? in QlikView

How can I calculate mean, standard deviation etc. over groups?

mlarruda — Fri, 29 Nov 2024 19:10:00 GMT

I have a sheet with two columns (MONTH and NAME) and I can easily make a table of names by month, setting Dimension = MONTH and Expression = COUNT(NAME).

Now I want to calculate mean and standard deviation of names for month (i.e. over the rows of the first paragraph's table). How can I do it?

Re: How can I calculate mean, standard deviation etc. over groups?

marksouzacosta — Sun, 01 Dec 2024 03:20:26 GMT

Hi @mlarruda,

Do you have a sample of your data to show us and also a sample of your expected results?

Regards,

Mark Costa

Re: How can I calculate mean, standard deviation etc. over groups?

Vegar — Sun, 01 Dec 2024 08:15:50 GMT

You could try to use aggr to get the value per month in your expression and then perform your desired aggregation on top of them, like below:

Avg(Aggr( COUNT(NAME) , MONTH))

Re: How can I calculate mean, standard deviation etc. over groups?

Padma123 — Mon, 02 Dec 2024 07:57:45 GMT

for mean -avg(aggr(sum(aggr(count(name),month)),month))

for standard deviation-stdev(aggr(sum(aggr(count(name),month)),month))

Re: How can I calculate mean, standard deviation etc. over groups?

mlarruda — Mon, 02 Dec 2024 20:40:37 GMT

Thanks for all answers. The solution of Vegar worked but my REAL problem is a bit more complex and I am not being able to adapt this solution.

Consider these data:

Month	Name	Country
January	Albert	USA
January	Barbara	Canada
January	Charlie	USA
January	Dana	Brazil
February	Fred	USA
March	Harry	USA
March	Albert	Brazil
April	Jane	Canada
April	Barbara	Brazil
April	Kurt	USA
April	Fred	Canada
April	Larry	USA
April	Monica	USA
May	Charlie	Brazil
May	Monica	Brazil
May	Paul	Canada

Doing a dynamic table with dimensions Month and Country and expression COUNT(Names) I got:

Month	USA	Canada	Brazil
January	2	1	1
February	1	-	-
March	1	-	1
April	3	2	1
May	-	1	2

So, my goal is to know the average (and st.dev.) of names by month, by country

I did a table with dimension Country and expression Avg(Aggr(COUNT(Names),Country, Month)) and the result was

Country	Avg
USA	1.75
Canada	1.33
Brazil	1.25

But note that, the result for Brazil is (1+1+1+2)/4 = 1.25, ignoring the empty months. And I want to consider the empty months as having zero names and so the result for Brazil might be (1+0+1+1+2)/5 = 1. Of course, the same applies for USA and Canada and for standard deviation.

How can I force the calculus to consider the empty months as having zero names?