Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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?

Thanks,

A

1 Solution

Accepted Solutions

Re: Complicated count

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

see attached example.


talk is cheap, supply exceeds demand
5 Replies

Re: Complicated count

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

see attached example.


talk is cheap, supply exceeds demand
Not applicable

Re: Complicated count

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

Highlighted
Not applicable

Re: Complicated count

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

Re: Complicated count

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Complicated count

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

Community Browser