Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

Tags (2)
14 Replies
MVP
MVP

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.

ogster1974
Honored Contributor II

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.

Not applicable

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

Not applicable

Re: Counts give Double the true value

Hi Andy

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

Thx

Graham

MVP
MVP

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?

And could you upload your source Excel file?

Not applicable

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 ...

MVP
MVP

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?

Not applicable

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

Community Browser