topic Re: Complicated count in QlikView

Complicated count

Fri, 26 Jul 2013 16:13:45 GMT

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,

Re: Complicated count

Gysbert_Wassenaar — Fri, 26 Jul 2013 16:19:36 GMT

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

see attached example.

Re: Complicated count

Fri, 26 Jul 2013 16:36:40 GMT

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.

Re: Complicated count

Fri, 26 Jul 2013 16:58:47 GMT

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,

Re: Complicated count

Gysbert_Wassenaar — Fri, 26 Jul 2013 17:00:17 GMT

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.

Re: Complicated count

Tue, 30 Jul 2013 15:53:27 GMT

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