How can obtain an average ?

[#1]

Hi all,

I have this file and I want to obtain for 2013 Average effective by service, so :

DIR COMM. = Sum(Janv+Fev+Mar+Avr+juin+Juil) / 7

Société	Service	Mois	Année	Effectifs
CORPA	DIR. COMM.	janv.	2013	3
CORPA	DIR. COMM.	févr.	2013	3
CORPA	DIR. COMM.	mars	2013	3
CORPA	DIR. COMM.	avr.	2013	2
CORPA	DIR. COMM.	mai	2013	2
CORPA	DIR. COMM.	juin	2013	2
CORPA	DIR. COMM.	juil.	2013	2
CORPA	DIR. PROJ.	janv.	2013	3
CORPA	DIR. PROJ.	févr.	2013	3
CORPA	DIR. PROJ.	mars	2013	3
CORPA	DIR. PROJ.	avr.	2013	9
CORPA	DIR. PROJ.	mai	2013	9
CORPA	DIR. PROJ.	juin	2013	9
CORPA	DIR. PROJ.	juil.	2013	9
CORPA	DIR. SUPP.	avr.	2013	1
CORPA	DIR. SUPP.	mai	2013	1
CORPA	DIR. SUPP.	juin	2013	1
CORPA	DIR. SUPP.	juil.	2013	1

Can someone has a simple expression ?

Thanks for your help.

[swuehl]

Just create a chart with dimension service and then either

=avg(Effectivs)

if your effectivs are aggregated by month and you want to consider the number of months available, or maybe otherwise

=sum(Effectivs) / 7

[#3]

Thanks but if I have 12 months and not 7 How can have a simple dynamic calculation?

[swuehl]

Using the avg() function or

=sum(Effectifs) / count(distinct Mois)

[hic]

See also http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/29/averages

HIC

[#6]

Henric I try : (Aggr(Sum(Effectifs) / count( distinct Mois),Société,Service,Mois))

I obtain 3,6 but not 2,4.

[hic · Accepted solution]

You have to have an aggregation function outside the aggr, too. And you don't need Société or Service as grouping symbols. Hence, one of the two following should work:

Sum( Effectifs ) / Count( distinct Mois )

Avg( Aggr( Sum( Effectifs ), Mois ) )

So, Swuehl's suggestion above is probably the best path forward.

HIC