topic Re: Category-wise aggregation in QlikView

Category-wise aggregation

datanibbler — Tue, 02 Jul 2013 13:31:25 GMT

Hi,

I want to construct a bar_chart with specific employee data - overtime by employee. I don't yet know quite how to do this. Maybe someone can help me here:

- Every employee has a specific nr. of hrs. of overtime.

- I want to group overtime_hrs. into categories - 1-10 hrs., 11-20 hrs., 21 to 30 hrs.

- Then I want to display these categories as dimension on the x-axis and the nr. of employees in that category on the y-axis.

I know that I can group values using the aggr function. I have already used it and there is an excellent example in my book "QlikView 11 for Developers" - only I want to take this another step further: Instead of a nr. of overtime_hrs. as one dimension_point, I want to use a range.

Thanks a lot!

Best regards,

DataNibbler

Re: Category-wise aggregation

Anonymous — Tue, 02 Jul 2013 13:36:37 GMT

If the groups of hours will be fixed and the user doesn't expect to change them, then I would use INTERVAL MATCH to create intervals that that can then be set as a dimension on your chart.

See page 241 of QV11 developer manual for an explanation of it.

Jonathan

Re: Category-wise aggregation

datanibbler — Tue, 02 Jul 2013 14:20:37 GMT

Hi jonbroughavone,

that seems a very good idea. Those groups will be fixed - I will use fixed categories (1-10, 11-20, 21-30 etc.). If I understood that chapter correctly, then I wouldn't need to use the AGGR function at all, but INTERVAL MATCH would be enough - I could have my intervals (a label, start and end for each) in a table and just use the interval_label as dimension and the count(distinct nr) as my formula. Right?

Thanks a lot!

Best regards,

DataNibbler

Re: Category-wise aggregation

Anonymous — Wed, 03 Jul 2013 07:29:11 GMT

Yes, sounds like you understand how to use it. No need for aggr(), the dimensions created by the interval match, and a count as an expression. That shoud then give you different bars for the different hour groupings.

Jonathan

Re: Category-wise aggregation

datanibbler — Wed, 03 Jul 2013 09:56:06 GMT

Thanks a lot, jonbroughavone!

That's all I want to display - no personell_numbers, nothing to identify individuals. I use the AutoNumber function in the script to avoid loading the original personell_number while still having some unique ID to count. I have asked myself what is actually the point when, for lack of any identificator, the person viewing this will afterwards have to ask HR - but in my own interest, I want to touch as little real HR data as possible.

Best regards,

DataNibbler