Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

justcotto
New Contributor

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

Re: Counting occurrences in a bar chart.

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
New Contributor

Re: Counting occurrences in a bar chart.

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.

Re: Counting occurrences in a bar chart.

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
New Contributor

Re: Counting occurrences in a bar chart.

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

Re: Counting occurrences in a bar chart.

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
New Contributor

Re: Counting occurrences in a bar chart.

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

Re: Counting occurrences in a bar chart.

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
New Contributor

Re: Counting occurrences in a bar chart.

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