topic Re: Average per department in QlikView

Average per department

Mon, 09 Dec 2013 11:29:10 GMT

Hi,

I want to be able to calculate an average sales per salesperson per department for my app. I have several different departments, each with a corresponding sales figure, and the number of people working in each department may also vary. Now I want to know how they stack up,and therefor calculate an average for them all, however I need to have an dynamic calculation since the different numbers may change.

I need a formula that looks at my the total sales for each department av then divides it by the total number of ppl working at that department. My formula looks like this (but i cant get it to work properly):

(SUM (Sales)/COUNT(DISTINCT[Salesperson]))

Cheers,

Nils

Re: Average per department

Mon, 09 Dec 2013 11:32:11 GMT

Try

(SUM (Sales)/ aggr(COUNT(DISTINCT[Salesperson]), Department) )

Re: Average per department

Mon, 09 Dec 2013 11:35:54 GMT

try this

SUM (Total Sales) / COUNT(Total [Salesperson])

Re: Average per department

Mon, 09 Dec 2013 11:48:35 GMT

thanks for all the replies, however im sorry but none of these seem to work. they are all returning blank tables. this feels like a no brainer (i do this in excel all the time). could you please have another look?

Re: Average per department

Anonymous — Mon, 09 Dec 2013 11:51:39 GMT

Please share some of your (dummy) data in a Qlikview document.

Re: Average per department

Mon, 09 Dec 2013 11:59:08 GMT

sorry, yeas, here it is....

Re: Re: Average per department

manideep78 — Mon, 09 Dec 2013 12:42:00 GMT

See the attached Qvw

Re: Average per department

Mon, 09 Dec 2013 13:12:57 GMT

excellent tnx!