Complicated count

[#1]

Hi All,

I am looking to complete a count on a particular field (Project Manager) and I suspect that I need to use the aggr() function and set analysis - however I don't know how exactly to do this! Help please.

I have a list of projects (c.300), each with a Project Manager. Projects are either active or inactive (as flagged by the 'Active' field).

What I want to be able to do is create a Straight Table which lists some project information, the project manager and a count of how many Active projects each Project Manager is working on.

Project Code	Project Name	Active	Start Date	Finish Date	Project Manager (PM)	No. of active Projects this PM has
001	Alpha	Yes	Jan 2013	Aug 2013	Joe Bloggs	2
002	Bravo	No	Sep 2012	Dec 2012	Joe Bloggs	2
003	Charlie	Yes	Mar 2013	Dec 2013	Jane Bloggs	1
004	Echo	Yes	Jul 2013	Mar 2014	Joe Bloggs	2
005	Foxtrot	Yes	May 2012	Nov 2013	Anne Other	1

Joe Bloggs has three projects but only two are active projects, so he has a '2'.

Jane and Anne have one active project each.

Any idea how I can do this?

Thanks,

A

[Gysbert_Wassenaar · Accepted solution]

count({<Active={'Yes'}>} total <[Project Manager (PM)]> [Project Code])

see attached example.

[#3]

Many thanks Gysbert. That is exactly what I require.

I am not clear as to why the Project Code field is used...

Would you mind telling elaborating a little on the construct of this function?

Thanks again.

A

[#4]

One other observation - I notice that the number that I get is limited by other contraints on the table. For example i have limited the table to show me only project which are due to finish in the next month and this gives me a different number to a table that displays all projects.

Is it possible to show all active projects?

Finally ( ! ), if I want to add a second condition to my set analysis for example to count only projects with a 'Red' status, would I add the text in bold below?

count({<Active={'Yes'}, Status={'Red'}>} total <[Project Manager (PM)]> [Project Code])

Tks,

A

[Gysbert_Wassenaar]

Project Code can be replaced with any other field. The count function just needs a field to count, it doesn't matter which field you use. The total keyword is necessary because you want the total count for each project manager.

[#6]

Hi Gysbert - many thanks for all of your help.

Do you have any idea why I might not be seeing the full set of results as per the below post?

From what I have read, I would have thought that the 'total' keyword would enforce this.

Thanks,

Alan