14 Replies Latest reply: Oct 30, 2015 3:19 PM by G Bortz RSS

    Counts give Double the true value

    G Bortz

      Hi

       

      I am using a Bar chart and am counting the number of instances in the category and am getting double the true value. The category is a numeric dimension ie Cost. I am also summing over the values in that category and getting double the number exactly.

       

      Any thoughts

       

      Regards

        • Re: Counts give Double the true value
          Stefan Wühl

          Have you checked your data model and script, that no duplication of data actually happended?

          A candidate for data duplication is e.g. a JOIN in the script.

          • Re: Counts give Double the true value
            Sunny Talwar

            May be we can help you better if you can provide a sample.

              • Re: Counts give Double the true value
                G Bortz

                Hi Sunny

                 

                I attach a file which shows the problem. There is no double scripting in my script as one of the helpers requested that I address.

                 

                What i actually want to do is to try do a histogram in Qliksense , by using a calculated dimension ie =Round(Value,BinSize)

                 

                So firstly I tried to do Round(Cost,1) , as the dimension and Count(Cost ) as the measure, which because cost is an integer should give me the same as Cost being the dimension , and Count (Cost ) being the measure. Both of these attempts gave me the same "wrong " answer ie counts of 38 when the truth was 19 , etc

                 

                Also when I summed the Value in the count bins ie Sum( Value) in the right hand graph , the answers are exactly 2 X the truth.

                 

                I managed to do a work around , by converting the Cost Integer to a characcter ( inside Excel)  and then repeating the Bar Chart AND IT WORKED!.

                 

                The problem is in doing histograms where the values may be real numbers with many significant figures , it would be clumsy to do this. I would have to go back to excell and reread the data in ..thats not really an app!

                 

                Qlikview  never had this problem , I could do many histograms using Round( Value, Binsize) as a calculated dimension , without having to convert to text.

                 

                So the real question is , and what I was really trying to do was do a Histogram on the Rep_% variable in my data , and tried to do it by:

                 

                1) Creating a calculated dimension : =Round(Rep_%,0.1) ,

                2) and a measure of Count( Rep_%)

                 

                I got totally wrong answers ie a reading of 792 in the first bin , where the total count is only 532

                 

                Your help would be much appreciated as histograms are essential for me , perhaps you can send me a few examples of histograms where the bin sizes are fractions ie 0.1 in with , between 0 and 1 ( percentages for example)

                 

                Thanks

                 

                Regards

                Graham

              • Re: Counts give Double the true value
                Andy Weir

                sounds like a data layer issue.  put your data you are looking to use in a straight table or use the data model viewer to check.

                • Re: Counts give Double the true value
                  G Bortz

                  My original Xcel file has 532 rows , what have I done to have loaded 1064 rows , how did you see my table has 1064 rows...if you show me how to see that ...YOU HAVE SOLVED MY PROBLEM..my table has been loaded twice ...

                    • Re: Counts give Double the true value
                      Stefan Wühl

                      G Bortz wrote:

                       

                      My original Xcel file has 532 rows , what have I done to have loaded 1064 rows , how did you see my table has 1064 rows...if you show me how to see that ...YOU HAVE SOLVED MY PROBLEM..my table has been loaded twice ...

                      Go to data model, click on the table header ('Sheet1') then open the preview with the upper right icon.

                      It's telling you 1064 rows are in the table.

                      By expanding the table to show all fields, you can examine the single fields by clicking on the field names.

                    • Re: Counts give Double the true value
                      G Bortz

                      Yes I have a script in the editor, but its only there once , how did this happen and how do I prevent it from happening again ?

                       

                      Thnx

                      Grham

                      • Re: Counts give Double the true value
                        G Bortz

                        I used the script editor and maybe reloaded using import from excell.

                         

                         

                        Should i therefore only use 1 method..the load editor.

                         

                        I should also  check the number of lines in the viewer.

                         

                        How can i  correct the situation? How do i delete the table ?. Or can i just go to the load editor And reload the table..making sure there is only 1 i nstance of the script.