topic Re: COUNT Specific Values in QlikView

COUNT Specific Values

Anonymous — Tue, 15 Aug 2017 13:40:00 GMT

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?

Re: COUNT Specific Values

sunny_talwar — Tue, 15 Aug 2017 15:50:04 GMT

You can create a calculated dimension

Dimension

Left(LOGIN, 1)

Expression

Count(Name)

Re: COUNT Specific Values

antoniotiman — Tue, 15 Aug 2017 15:51:22 GMT

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

Re: COUNT Specific Values

vishsaggi — Tue, 15 Aug 2017 15:52:14 GMT

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

Re: COUNT Specific Values

ecolomer — Tue, 15 Aug 2017 15:55:59 GMT

Is this you need?

Re: COUNT Specific Values

ecolomer — Tue, 15 Aug 2017 16:20:42 GMT

Re: COUNT Specific Values

Anonymous — Tue, 15 Aug 2017 16:52:16 GMT

This works great. Thanks!

Re: COUNT Specific Values

Anonymous — Tue, 15 Aug 2017 17:12:13 GMT

yes, that works too. Thanks for the help!

Re: COUNT Specific Values

ecolomer — Tue, 15 Aug 2017 17:36:46 GMT

Mark response as correct and usefull.

Thank's

Re: COUNT Specific Values

Anonymous — Thu, 17 Aug 2017 19:09:21 GMT

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?

Re: COUNT Specific Values

vishsaggi — Thu, 17 Aug 2017 19:24:10 GMT

Use this?

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

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

Re: COUNT Specific Values

Anonymous — Thu, 17 Aug 2017 19:34:04 GMT

yes, that works! thank you!!!