topic Re: Counting occurrences in a bar chart. in QlikView

Counting occurrences in a bar chart.

justcotto — Fri, 27 Oct 2017 16:17:05 GMT

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

Re: Counting occurrences in a bar chart.

Nicole-Smith — Fri, 27 Oct 2017 16:29:24 GMT

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.

Re: Counting occurrences in a bar chart.

justcotto — Fri, 27 Oct 2017 16:41:37 GMT

When I did that it returned this:

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

Re: Counting occurrences in a bar chart.

Nicole-Smith — Fri, 27 Oct 2017 17:37:43 GMT

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.

Re: Counting occurrences in a bar chart.

justcotto — Fri, 27 Oct 2017 17:45:31 GMT

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

This is where I am getting the error

Re: Counting occurrences in a bar chart.

Nicole-Smith — Fri, 27 Oct 2017 18:30:01 GMT

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.

Re: Counting occurrences in a bar chart.

justcotto — Fri, 27 Oct 2017 18:55:35 GMT

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

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

Re: Counting occurrences in a bar chart.

Nicole-Smith — Fri, 27 Oct 2017 20:53:00 GMT

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

Re: Counting occurrences in a bar chart.

justcotto — Fri, 27 Oct 2017 21:04:26 GMT

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