9 Replies Latest reply: Jun 10, 2013 6:46 AM by Friedrich Hofmann RSS

    Aggregating by ranges

    Friedrich Hofmann

      Hi,

       

      I would like to display data by categories (such as the book "QlikView 11 for Developers" explains in a chapter "nested aggregation with aggr()).

      Background: In that chapter, the dimension is the nr. of flights a carrier serves and the formula is the nr. and name(s) of the respective carrier(s).

      I would like to do this a little different: We have about 850 employees and I want to display the nr. of overtime-hrs. per employee - but there might be up to 850 distinct values in the "nr. of overtime hrs" part - probably less, but even 100 is too much, making the display very confusing.

      Instead, I want to build categories like "1-10 hrs.", "11 to 20 hrs." etc.

      Can anyone tell me how I have to modify the aggr() function to accomplish that?

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Aggregating by ranges
          vikas mahajan

          Expression

           

          AGGR(RANK(SUM(VALUE)),MONTH,DIM)

           

          Here I use month and dim 2 dimensions chage as per your requirement.

           

          Vikas

          • Re: Aggregating by ranges
            vikas mahajan

            Request you to create small demo and attach to help you in better. what exact you want

             

             

            Vikas

              • Re: Aggregating by ranges
                Friedrich Hofmann

                Hi Vikas,

                 

                creating demo files is always a fickle task in my case since all the data I'm dealing with is sensitive, all the more this time as I'm handling personell data. Also, it takes time which I never have. Still, I have made a small Excel file for you that will at least show you what I want to see at the end of it.

                 

                In addition to this diagram, I will need some possibility to drill down into one category and see the actual personell_numbers so that the respective employees can be identified.

                 

                The formula is not difficult - a sum(distinct count(personell_nr)) will do I guess. The difficult part is the dimension.

                I have to load all records anyway, so I guess I can easily attach a table_diagram with the details (the individual personell_numbers).

                 

                Best regards,

                 

                DataNibbler

                • Re: Aggregating by ranges
                  Friedrich Hofmann

                  Hi Vikas,

                   

                  creating demo files is always a fickle task in my case since all the data I'm dealing with is sensitive, all the more this time as I'm handling personell data. Also, it takes time which I never have. Still, I have made a small Excel file for you that will at least show you what I want to see at the end of it.

                   

                  In addition to this diagram, I will need some possibility to drill down into one category and see the actual personell_numbers so that the respective employees can be identified.

                   

                  The formula is not difficult - a sum(distinct count(personell_nr)) will do I guess. The difficult part is the dimension.

                  I have to load all records anyway, so I guess I can easily attach a table_diagram with the details (the individual personell_numbers).

                   

                  Best regards,

                   

                  DataNibbler

                  • Re: Aggregating by ranges
                    Friedrich Hofmann

                    Hi Vikas,

                     

                    creating demo files is always a fickle task in my case since all the data I'm dealing with is sensitive, all the more this time as I'm handling personell data. Also, it takes time which I never have. Still, I have made a small Excel file for you that will at least show you what I want to see at the end of it.

                     

                    In addition to this diagram, I will need some possibility to drill down into one category and see the actual personell_numbers so that the respective employees can be identified.

                     

                    The formula is not difficult - a sum(distinct count(personell_nr)) will do I guess. The difficult part is the dimension.

                    I have to load all records anyway, so I guess I can easily attach a table_diagram with the details (the individual personell_numbers).

                     

                    Best regards,

                     

                    DataNibbler

                    • Re: Aggregating by ranges
                      Friedrich Hofmann

                      Hi Vikas,

                       

                      creating demo files is always a fickle task in my case since all the data I'm dealing with is sensitive, all the more this time as I'm handling personell data. Also, it takes time which I never have. Still, I have made a small Excel file for you that will at least show you what I want to see at the end of it.

                       

                      In addition to this diagram, I will need some possibility to drill down into one category and see the actual personell_numbers so that the respective employees can be identified.

                       

                      The formula is not difficult - a sum(distinct count(personell_nr)) will do I guess. The difficult part is the dimension.

                      I have to load all records anyway, so I guess I can easily attach a table_diagram with the details (the individual personell_numbers).

                       

                      Best regards,

                       

                      DataNibbler