26 Replies Latest reply: Jul 6, 2016 7:36 AM by Anil Babu Samineni RSS

    How to do a histogram?

      Hi everyone!

       

      I am not used to use QlikVio. Actually, this techonolgy is new for me, but in the future I will use it more often. Could you give some help?

       

      I am lost in the midle of so much information and I am still trying to figure out how to work with this program. Can you give me the steps how to do a simple histogram?

       

      I have a Data with numbers from 0 to 20. I want to do a histogram which counts how many numbers are in specifics times periods, such as:

       

      ]0,1[

      ]1,2[

      ]2,3[

      ]3,6[

      ]6,10[

      ]10,20[. How can I do it? I tried to calculate an dimension using if and then do a count for the data but it doesnt work.

       

       

      I will grateful if some one could help me out.

       

      Thanks in advance,

      Best,

      Pedro Pereira

        • Re: How to do a histogram?
          Alessandro Saccone

          This is the script you need

           

          AAA:
          LOAD * Inline [
          Field
          '0,1'
          '1,2'
          '2,3'
          '3,6'
          '6,10'
          '10,20'
          ]
          ;

          BBB:
          NoConcatenate
          LOAD Field, Mid(Field, Index(Field, ',')+1)-Left(Field, Index(Field, ',')-1) as difference Resident AAA;
          DROP Table AAA;

            • Re: How to do a histogram?

              First, thanks for your answear!

               

              Assuming I am a newbie, could you elaborate more about the objects (Inline, difference)? Could you explian in more detail?

               

              What does "field" stand off? Where do you the count on your script?

               

              Sorry to bother you that much, but as I said this software is new for me.

                • Re: How to do a histogram?
                  Alessandro Saccone

                   

                  You can load data from Database, External files or in memory, this is the case of Load * inline Syntax,
                  I'm loading a field called Field with the values listed below.

                  Then I load again the same data (BBB) but I add a field:
                  ... Mid(Field, Index(Field, ',')+1)-Left(Field, Index(Field, ',')-1) as difference ...

                  Where
                  Index(Field, ',') return the position of the comma between the couple of data (ex. 1,2 index returns 2, the position of comma)
                  Mid(...) returns a string from position Index(Field, ',')+1 to the end of the string (Field) so it returns for the first record 2
                  Left(...) returns a string from the starting point to the position Index(Field, ',')-1

                  So now I have the 2 numbers and do first-second as difference

                  hope this helps

                   

                   

                   

              • Re: How to do a histogram?
                Sasi Kumar

                Hello,

                The steps to create a Histogram,

                1. Create an Input Box. In its properties, create a new variable called BinWidth. Click OK.
                2. Set BinWidth to 1 in the Input Box.
                3. Create a Bar Chart with a calculated dimension, using =Round(Value, BinWidth)
                4. Set the label for the calculated dimension to “Measurement”. Click Next.
                5. Use Count(Value) as expression. Click Next.
                6. Sort the calculated dimension numerically. Click Next three times.
                7. On the “Axes” page, enable “Continuous” on the Dimension Axis. Click Next.
                8. On the “Colors” page, disable the “Multicolored” under Data appearance. Click Finish.

                Input box.png You should now have a histogram. If you have too few bars, you need to make the bin width smaller. If you have too many, you should make it bigger. In order to make the histogram more elaborate you can also do the following:

                • Add error bars to the bins. The error (uncertainty) of a bar is in this case the square root of the bar content, i.e. Sqrt(Count(Value))
                • Add a second expression containing a Gaussian curve (bell curve):
                  • Convert the chart to a Combo chart
                  • Use the following as expression for the bell curve:
                    Only(Normdist(Round(Value,BinWidth),Avg(total Value),Stdev(total Value), 0))*BinWidth*Count(total Value)
                  • Use bars for the measurement and line for the curve.

                 

                Histogram2.png

                 

                With these changes, you can quickly assess whether the measurements are normally distributed or whether there are some anomalies.

                • Re: How to do a histogram?
                  Henric Cronström

                  A complete description can be found in this Article: Recipe for a Histogram

                   

                  HIC

                   

                  @ sasi kumar:

                  It would be better if you link to the original article instead of copying someone else's work and using it as your own answer.

                    • Re: How to do a histogram?
                      Sasi Kumar

                      @Henric:

                      Sure. It was a coincidence which i referred your article last week. Hence pasted the content which helped me. Sorry if that hurt you.

                      • Re: How to do a histogram?

                        What I wanted was buckets.

                         

                        Do you know how can I sort my graph in ascending? I already try every option from the sort window.

                         

                        Thanks for the help. Here is my code and my output.

                         

                        This is the code on calculated dimension:

                         

                        = 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 and Diferença <=4,'3-4',

                         

                        If(Diferença>4 and Diferença <=5,'4-5',

                         

                        If(Diferença>5 and Diferença <=6,'5-6',

                         

                        If(Diferença>6 and Diferença <=7,'6-7',

                         

                        If(Diferença>7 and Diferença <=8,'7-8',

                         

                        If(Diferença>8 and Diferença <=9,'8-9',

                         

                        If(Diferença>9 and Diferença <=10,'9-10',

                         

                        If(Diferença>10,'>10',

                         

                        )))))))))))

                         

                        And my output is like that:

                         

                        Graph.bmp

                        How can I sorted this by the order I put on the code?

                          • Re: How to do a histogram?
                            John Cavoulas

                            The odd bin of '>10' doesn't allow for easy sorting so you have to get creative with 'Dual', then sort numerically;


                            =

                            If(Diferença<=1 ,Dual('0-1',0),

                            If(Diferença>1 and Diferença <=2,Dual('1-2',1),

                            If(Diferença>2 and Diferença <=3,Dual('2-3',2),

                            If(Diferença>3 and Diferença <=4,Dual('3-4',3),

                            If(Diferença>4 and Diferença <=5,Dual('4-5',4),

                            If(Diferença>5 and Diferença <=6,Dual('5-6',5),

                            If(Diferença>6 and Diferença <=7,Dual('6-7',6),

                            If(Diferença>7 and Diferença <=8,Dual('7-8',7),

                            If(Diferença>8 and Diferença <=9,Dual('8-9',8),

                            If(Diferença>9 and Diferença <=10,Dual('9-10',9),

                            If(Diferença>10,Dual('>10',10)

                            )))))))))))

                              • Re: How to do a histogram?

                                Thanks you very much!

                                 

                                Could you tell why it wasnt working? And why e.g. 2-3 was first than 1-2?

                                 

                                PS. I learning how to work with Qlikview I am think if I understand deeply how Qlikview works, I'll learn faster and better.

                                  • Re: How to do a histogram?
                                    John Cavoulas

                                    Pedro, I'm not sure why yours wasn't sorting correctly the first time...I did not see your sort criteria. It's possible neither text nor numeric were checked.

                                     

                                    The reason Dual works is it assigns a numeric value that you determine for the text value. So, since your dimension has been hard-coded it was easy enough to add the numeric value based on a known order desired. Notice the last value for each bin cycles 0 through 10. You should look up 'Dual' in the help to see a fuller explanation.

                                     

                                    Glad it helped. BTW, this method isn't best done in the front end. I'd prefer it under the hood in the script. If you find the document being slowed down during selections you may want to look into that.

                              • Re: How to do a histogram?
                                Kumar Pramod

                                Hi henric,

                                 

                                Would you please explain the steps how to create a "bell curve" in qlik sense.

                                 

                                As this post is useful to know about the histogram(bell curve) finding difficult to use it in qlik sense.

                                because,

                                1. we don't have input box in qlik sense and you asked to create a new variable with default value 1, but again you assigned 0.2 to it. This is confusing. Is we should use 1 for all types of data or it should be changed according to data.

                                2. In steps 7 you mentioned Axes page. this page we can't find in qlik sense.

                                3. In the expression for line:

                                  Only(Normdist(Round(Value,BinWidth),Avg(total Value),Stdev(total Value), 0))*BinWidth*Count(totalValue)

                                 

                                You have used Round(), Avg(), Stdev(), count(), why we are using this what is the functionality of this.

                                 

                                I have attached the sample data and .qvf you check the file and confirm is it correct? if wrong explain where i am going wrong.

                                 

                                Please explain the steps how to do this in Qlik sense it will be helpful.

                                 

                                 

                                Thanks,

                                Pramod