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: 
justcotto
Contributor III
Contributor III

Counting occurrences in a bar chart.

Hello,

I have a chart that records employee information.  There is a column labeled Record_Type that labels each employee as HIRE, TERM, TRANSFER or HEADCOUNT.  I want to make a table and chart that displays the number of Hires and Terminations by Business Unit.  I am brand new to Qlik and was wondering what statement I could use to count hires and terminations while excluding the others.  Initially I thought to use GetSelectedCount but I am not sure how to approach this problem.


Thank you

8 Replies
Nicole-Smith

Your chart should be set up something like this:

Dimension:

Business_Unit

Expressions:

1.  count({<Record_Type={'HIRE'}>} distinct Employee_ID)

2.  count({<Record_Type={'TERM'}>} distinct Employee_ID)


The set analysis is what takes care of filtering out the other values.

justcotto
Contributor III
Contributor III
Author

When I did that it returned this:

Capture.PNG

Also when I try to insert that second line for 'TERM', it gives me and error saying garbage after count.

Nicole-Smith

Case of the field name matters, and it looks like your field is named RECORD_TYPE and not Record_Type:

1.  count({<RECORD_TYPE={'HIRE'}>} distinct Employee_ID)

2.  count({<RECORD_TYPE={'TERM'}>} distinct Employee_ID)


Make sure you name the employee ID field properly too.

justcotto
Contributor III
Contributor III
Author

Well I did change the statement for it to be applicable. I forgot to mention that in my response.

Capture.PNG

This is where I am getting the error

Capture2.PNG

Nicole-Smith

You want to add two separate expressions.  Add the first one, hit OK, and then Add again.  The first one will count hires and the second will count terms, so name them appropriately.

justcotto
Contributor III
Contributor III
Author

I appreciate you responding so quickly but I'm still not getting a correct count.

dimensions.PNGExpressions.PNGtable.PNG

It should look like this.  (when I filter data manually)

shouldlook.PNG

Nicole-Smith

It's because you're using Business Unit in your expressions instead of an Employee ID, which I figured you would have for each person that was hired or terminated.  Using employee ID would be the most accurate way to count because you can add the distinct on there.  Without the distinct, you could be duplicating rows, and therefore duplicating your counts if you have a bad join in your load script.  With what you've provided me, the following expressions are what you need, but I would still suggest using an Employee ID with the distinct keyword instead.

1.  count({<RECORD_TYPE={'HIRE'}>} BUSINESS_UNIT)

2.  count({<RECORD_TYPE={'TERM'}>} BUSINESS_UNIT)


I highly suggest you go through some of the free training provided by Qlik to understand more about the product.  Here is a link to get you started: QlikView Personal Edition On-Demand Webinar

justcotto
Contributor III
Contributor III
Author

Thanks I appreciate the help. Today is my first day using the product.