COUNT Specific Values

[Anonymous]

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? 

[sunny_talwar]

You can create a calculated dimension

Dimension

Left(LOGIN, 1)

Expression

Count(Name)

[antoniotiman]

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 · Accepted solution]

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))

[ecolomer]

Is this you need?

[ecolomer]

[Anonymous]

This works great. Thanks!

[Anonymous]

yes, that works too. Thanks for the help!

[ecolomer]

Mark response as correct and usefull.

Thank's

[Anonymous]

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?