topic Re: How can obtain an average ? in QlikView

How can obtain an average ?

Thu, 05 Sep 2013 12:48:37 GMT

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.

Re: How can obtain an average ?

swuehl — Thu, 05 Sep 2013 12:51:43 GMT

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

Re: How can obtain an average ?

Thu, 05 Sep 2013 12:57:29 GMT

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

Re: How can obtain an average ?

swuehl — Thu, 05 Sep 2013 12:59:31 GMT

Using the avg() function or

=sum(Effectifs) / count(distinct Mois)

Re: How can obtain an average ?

hic — Thu, 05 Sep 2013 13:09:21 GMT

HIC

Re: How can obtain an average ?

Thu, 05 Sep 2013 13:25:38 GMT

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

I obtain 3,6 but not 2,4.

Re: How can obtain an average ?

hic — Thu, 05 Sep 2013 13:30:58 GMT

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