5 Replies Latest reply: Feb 2, 2015 10:25 AM by Pedro Pereira RSS

    How to make a Histogram chart with the given data?

      Hi All,

       

      My data is in this format:

       

       

      Column1(Country)         Column2(Product)        Column3(Netsales)

       

      A                                         Dove                       250

      A                                        Sunsilk                     100

      B                                        Panteen                   300

       

       

      and the list goes on, now what i want is to plot a histogram chart.

      Whenever i select a country i should get all the products under that country as bar where each bar represents a product and the BIN should be below avg, avg and above average.

       

      Please reply if anyone has any idea, if possible tell me the expressions and dimenssions as i don't have Licensed version of QV.

       

       

       

      Many thanks in Advance

       

      Thanks and Regards

      Sagar Gupta

        • How to make a Histogram chart with the given data?
          Stefan Wühl

          I haven't fully understood what you mean with '...all products under that country as bar where each bar represents a product and the BIN should be below avg, avg and above average.', i.e. showing all countries and showing bins seems to me a bit controversial.

          Maybe you could post the expected outcome for the above, or a somewhat more complex, sample data (as hand-drawing, excel table etc.)

           

          Regards,

          Stefan

            • Re: How to make a Histogram chart with the given data?

              Thanks for the Reply

               

              Here is what i want, say 1 have 10 counties and each country has 20 products(May be distinct or may not be), now if i select a country from the list box i should get a histogram which consist of products which fall in the selected country(represented as bars) and my X axis should have three buckets:

               

              1: Below average(all prducts whose sales is below average, say: 0-120)

              2: Average( all products whose sales are average, say: 120-300)

              3: Above average(all products whose sales is above average, say: 300+)

               

              Hope this will make you clear about my requirement.

               

              Thanks a lot again for replying

               

              Regards

              Sagar Gupta

                • Re: How to make a Histogram chart with the given data?
                  Stefan Wühl

                  Sagar Gupta,

                   

                  you could create a calculated dimension for your bar chart, something like

                   

                  =aggr(

                  if(sum(Sales) <= avg(total aggr(sum(Sales),Product))-$(vRangeAvg),'Below avg -$(vRangeAvg)'

                  ,if(sum(Sales) <= avg(total aggr(sum(Sales),Product))+$(vRangeAvg),'avg +-$(vRangeAvg)'

                  ,'above avg+$(vRangeAvg)')),

                  Product)

                   

                  and as expression in your bar chart:

                  =count(distinct Product)

                   

                  The calculated dimension is grouping your products into 3 bins.

                   

                  I created an input box with a variable vRangeAvg to control the width of the average+-X bin.

                   

                  You could also create something similar in the chart, but if you need to be selection sensitive (i.e. selection on Products / products group), I would go for the calculated dimension.

                   

                  I know that you can't open the file, but maybe anybody elser want to see a sample, so I am attaching one.

                   

                  Regards,

                  Stefan

                    • Re: How to make a Histogram chart with the given data?

                      I am trying to use your examlpe to solve my problem but I can do it.

                      I have data (Diferença) between some value and I want to count the value between 0-1, 1-2, 2-3 and 3 or greater.

                       

                      I used this code to the calculated dimension:

                       

                      =Aggr(if(Diferença<1, '0-1',

                      if(Diferença>1 and Diferença<=2, '1-2',

                      if(Diferença>2 and Diferença<=3, '2-3',

                       

                      If(Diferença>3, '>3')))),Diferença).

                       

                      On the chart it says 'No numeric values on x-axis'. Can someone help me?

                • Re: How to make a Histogram chart with the given data?

                  Hi, I think if you using the Class() function will help make this alot easier.  Here is a quick copy/paste from the manual:

                   

                  class(expression, interval [ , label [ , offset ]])

                  Creates a classification of expressions. The bin width is determined by the number set as interval. The result

                  is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an

                  arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be

                  changed by adding an offset.

                   

                  Examples:

                  class( var,10 ) with var = 23 returns '20<=x<30'

                  class( var,5,'value' ) with var = 23 returns '20<= value <25'

                  class( var,10,'x',5 ) with var = 23 returns '15<=x<25'