6 Replies Latest reply: Mar 27, 2015 10:56 AM by Benny Lin RSS

    finding out top X numbers and bottom Y numbers

      hi guys

       

      http://i.imgur.com/m6aXy.jpg

       

      the above screenshot is the scrambled version of my table

       

      i would like to find out the top X utlized and bottom Y underutilized of linceses grouped by the license group and site

       

      how could i go about doing that?

        • finding out top X numbers and bottom Y numbers
          Leonard Short

          Have you tried using the rank() function? i.e. rank([Licences Used] / [Total Licenses])

            • Re: finding out top X numbers and bottom Y numbers

              hi leonard thanks for your reply,

               

              assuming i wanna find out the top/bottom 5 utilizations...

               

              i currently have a chart with a drill down group (Site+License Group)

               

              as for the expression,

               

              =(if(aggr(Rank(aggr(

              sum(LIC_USED)

              /sum(TOTAL_LIC)

              , LICENSE_GROUP, Site)), LICENSE_GROUP)<= 5 or

              aggr(Rank(aggr(

              sum(LIC_USED)

              /sum(TOTAL_LIC)

              , LICENSE_GROUP, cSite)), LICENSE_GROUP) >=

              (Max(total aggr(Rank(aggr(

              sum(LIC_USED)

              /sum(TOTAL_LIC)

              , LICENSE_GROUP, Site)), LICENSE_GROUP))-5)

              , sum(LIC_USED)

              /sum(TOTAL_LIC)))

               

              but the chart doesnt seem to be working properly as i wanna show the utilizations for each site

               

              when they drill down the site, it should show me the top/bottom 5 license groups of the respective sites

               

              i think my expression needs some adjustments but i cant figure out what...

               

              im unable to attach/upload anything right now because of some restrictions in my current location

                • Re: finding out top X numbers and bottom Y numbers
                  Leonard Short

                  My initial thought here is that you are trying to make this way too complicated for yourself. Also I have run into several instances where the AGGR function doesn't work at all depending on which version of QV you are using so I tend to avoid using it. Plus it is a memory hog.

                   

                  If you use rank([Licences Used] / [Total Licenses]) in the table above you will get the utilization ranking for each date at each site.

                   

                  Change the above to rank(sum([Licences Used]) / sum([Total Licenses])) it will give you the utilization rank across all of your dimensions.

                   

                  If you use a straight table for your chart type you can then go into the presentation tab and tell it to only display 5 rows and group all the others (or not show them at all), then just sort the table ascending by the rank to get the top 5, sort it descending to get the bottom five.

                   

                  If you need to use something other than a straight table then we will likely need to get into a bit of set analysis.

                    • Re: finding out top X numbers and bottom Y numbers

                      i have modified my expression to the following with 2 seperate charts instead of a single chart showing the top X and bottom Y and to simply show the top X and bottom Y across all sites instead of respective sites,

                       

                      =(if(aggr(Rank(aggr(

                      sum(LIC_USED)

                      /sum(TOTAL_LIC)

                      , LICENSE_GROUP))

                      , LICENSE_GROUP)<= 10

                      , sum(LIC_USED)

                      /sum(TOTAL_LIC)))

                       

                      =(if(aggr(Rank(aggr(

                      sum(LIC_USED)

                      /sum(TOTAL_LIC)

                      , LICENSE_GROUP))

                      , LICENSE_GROUP) >=

                      (Max(total aggr(Rank(aggr(

                      sum(LIC_USED)

                      /sum(TOTAL_LIC)

                      , LICENSE_GROUP))

                      , LICENSE_GROUP))- 10)

                      , sum(LIC_USED)

                      /sum(TOTAL_LIC)))

                • This reply has been hidden. This can happen if the message has been hidden by a moderator, or has been reported as abusive.