Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Champion III
Champion III

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

View solution in original post

11 Replies
sunny_talwar

You can create a calculated dimension

Dimension

Left(LOGIN, 1)

Expression

Count(Name)

antoniotiman
Master III
Master III

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
Champion III
Champion III

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
Master II
Master II

Is this you need?

P_271254.png

ecolomer
Master II
Master II

P_271254.png

Anonymous
Not applicable
Author

This works great. Thanks!

Anonymous
Not applicable
Author

yes, that works too. Thanks for the help!

ecolomer
Master II
Master II

Mark response as correct and usefull.

Thank's

Anonymous
Not applicable
Author

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?