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

# Counts give Double the true value

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

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

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

• ###### Re: Counts give Double the true value

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

The app seems to show correct results to me. Your data model table shows 1064 rows, each row showing a Cost values (but there are duplicate values, as you know). The count of Cost in bar chart seems to match that.

You are saying, you expect 19 instead of 38. Can you tell why you expect 19?

• ###### Re: Counts give Double the true value

Maybe the assumed data duplication is caused by the automatic generated script code (check the data editor), loading the source data twice, auto-concatenating?

• ###### Re: Counts give Double the true value

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

Hi Andy

Only got a single Table in the Viewer , have sent my file through

Thx

Graham

• ###### Re: Counts give Double the true value

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

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

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 wrote:

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

Have you used the wizard to import the excel? And maybe also copied script code from another app?

• ###### Re: Counts give Double the true value

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.

• ###### Re: Counts give Double the true value

swuehl,

Thank you very much for all your help and guidance

Regards

Graham