4 Replies Latest reply: Jul 3, 2013 5:56 AM by Friedrich Hofmann RSS

    Category-wise aggregation

    Friedrich Hofmann

      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
          Jonathan Brough

          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
              Friedrich Hofmann

              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
                  Jonathan Brough

                  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
                      Friedrich Hofmann

                      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