4 Replies Latest reply: Feb 17, 2015 5:59 AM by Marcus Sommer RSS

    Grouping Values on list box

      Hi all,

       

      I'm new to using QV and this might be a stupidly simple question but would really like your help here.

       

      I am creating a dashboard to show difference between budget and actual spending for each entity within a business (there are about 50). I  created a list box showing this difference, but instead of displaying individual difference  (e.g.  -93322.00, -84466.00, -83492.00,-27418.00,0,123.00 ...and so on ), I want to show by intervals such as -100,000--50,000, -50,000-0, 0-50,000 etc.

       

      Is this possible?

       

      Thanks for your help in advance.:)

       

      Michelle

        • Re: Grouping Values on list box
          Marcus Sommer

          You could cluster such calculation with class() or use a rounding-function, maybe round(value, 10000).

           

          - Marcus

            • Re: Grouping Values on list box

              Thanks Marcus, I think the class( ) function is closer to what I'm trying to achieve. With the above example though I have entered the expression as Class (Difference, 25000); on the list box it would still show the actual value then the class e.g. -93322.00     -94000.00<=00.00<-69,000,  -84466.00  -94000.00<=00.00<-69,000

               

              Do you know how I could get rid of the actual value and only show the each class once?

               

              Thank a lot again

                • Re: Grouping Values on list box
                  Marcus Sommer

                  If you want to use such expression-clustering directly within the gui in a listbox or a chart you will need a wrapping from these expression with aggr(), maybe in this way:

                   

                  = Class(aggr(EXPRESSION, DIMENSION), CLUSTER-SIZE, START

                   

                  or more practical

                   

                  = Class(aggr(sum(KPI) / Count(DISTINCT Month&Year), Store), $(Cluster), 'x', 0)

                   

                  whereby $(Cluster) is a variable to increase the flixibility.

                   

                  - Marcus

              • Re: Grouping Values on list box

                Hi Michelle,

                 

                It is possible using intervalmatch() function at script level. Then you can show intervals in listbox as desired. Hope this helps.

                 

                Regards