Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Double Counting in Straight Table Formulae

I have created a straight table and a cumulative total column using the following formula:

=RangeSum (Above(TOTAL(([Units])),0,NoOfRows(TOTAL)))

I have then tried to create a series of columns that do the same thing for individual stock items:

=Rangesum(Above(TOTAL(sum({$<[Code]={"CPG"}>}[Units])),0,NoOfRows(TOTAL)))

My problem is that the individual stock columns are subject to random double counting of some individual transactions.

Can anybody see why?

Keith

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

This is probably due to some characteristic of your data model, typically (but not only) when the table containing the code and table containing the total have different levels of granularity. I suggest that you post a sample of your model for more detailed analysis.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

9 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

This is probably due to some characteristic of your data model, typically (but not only) when the table containing the code and table containing the total have different levels of granularity. I suggest that you post a sample of your model for more detailed analysis.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Do I attach the file as an image? Or have I missed something?

rustyfishbones
Master II
Master II

Keith you can attach a file by selecting Use advanced Editor, like below image

2014-03-19_1240.png

Then select attach

2014-03-19_1242.png

Anonymous
Not applicable
Author

Great thanks.

rustyfishbones
Master II
Master II

your welcome!

Anonymous
Not applicable
Author

The process of creating a sample file has cleared the problem.

This suggests that it is something to do with my tables. I am not sure what it is or how I will account for it but this is a separate problem.

thanks for your help.

jonathandienst
Partner - Champion III
Partner - Champion III

Check all the joins in your load script. Its possible that one of them has duplicate keys on one side of the join, doubling up the records.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

There is still something to correct and it seems to lie in this area.

I shall have to learn more about my scripting.

Thanks,

Keith

On Wed, Mar 19, 2014 at 2:06 PM, Jonathan Dienst

Anonymous
Not applicable
Author

I am afraid that I am missing something obvious.

I have created a sample file which has simplified my script and I am still getting the double counting. It appears to start correctly and from April 13, 1992 it seems to begin double counting some stocks but not others.

Can anyone see what I am missing?

Keith