5 Replies Latest reply: Jul 30, 2013 11:53 AM by Alan Hendrickx RSS

    Complicated count

      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 CodeProject NameActiveStart DateFinish DateProject Manager (PM)No. of active Projects this PM has
      001AlphaYesJan 2013Aug 2013Joe Bloggs2
      002BravoNoSep 2012Dec 2012Joe Bloggs2
      003CharlieYesMar 2013Dec 2013Jane Bloggs1
      004EchoYesJul 2013Mar 2014Joe Bloggs2
      005FoxtrotYesMay 2012Nov 2013Anne Other1


      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?