Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
karlykrebs
New Contributor III

COUNT Specific Values

Trying to get a total count depending on a specific value within a string . Here is sample data I'm working with:

Manager:                         LOGIN:                      Name:

John Smith                      C0001                        Matt

John Smith                      E0001                        Chris

John Smith                      E0002                        Jack

Jane Anderson                C0002                        Mary

Jane Anderson                E0003                        Pat

Jane Anderson                E0004                        Jamie

I need to count how many contractors (identified by 'c' in login) and employees (identified by 'e' in login) each manager is assigned to. For example, If Jane Anderson was selected it would calculate that there are 2 employees and 1 contractor. How can I write this expression in a Statistics Box or Chart? 

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: COUNT Specific Values

Or in a text box like

= 'Contractor Count : ' & Count(IF(Left(LOGIN, 1) = 'C', 1)) & Chr(13) &

   'Employee  Count : ' & Count(IF(Left(LOGIN, 1) = 'E', 1))

Capture.PNG

11 Replies

Re: COUNT Specific Values

You can create a calculated dimension

Dimension

Left(LOGIN, 1)

Expression

Count(Name)

antoniotiman
Honored Contributor III

Re: COUNT Specific Values

Hi Karly,

maybe this

LOAD *,Left(LOGIN,1) as Type

From Table;

Table in Front End

Dimension : Manager

Expression :

Count({<Type={E}>} LOGIN //  Employees

Count({<Type={C}>} LOGIN)   //  Contractors

Regards,

Antonio

vishsaggi
Esteemed Contributor III

Re: COUNT Specific Values

Or in a text box like

= 'Contractor Count : ' & Count(IF(Left(LOGIN, 1) = 'C', 1)) & Chr(13) &

   'Employee  Count : ' & Count(IF(Left(LOGIN, 1) = 'E', 1))

Capture.PNG

ecolomer
Honored Contributor II

Re: COUNT Specific Values

Is this you need?

P_271254.png

ecolomer
Honored Contributor II

Re: COUNT Specific Values

P_271254.png

karlykrebs
New Contributor III

Re: COUNT Specific Values

This works great. Thanks!

karlykrebs
New Contributor III

Re: COUNT Specific Values

yes, that works too. Thanks for the help!

ecolomer
Honored Contributor II

Re: COUNT Specific Values

Mark response as correct and usefull.

Thank's

karlykrebs
New Contributor III

Re: COUNT Specific Values

The data has a couple instances where the login has 'c' instead of 'C', which is messing up the totals. Is there a way to include this in the expression above? Or does another expression need to be used?