topic Re: Double Counting in Straight Table Formulae in QlikView

Double Counting in Straight Table Formulae

Anonymous — Wed, 19 Mar 2014 11:42:01 GMT

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

Re: Double Counting in Straight Table Formulae

jonathandienst — Wed, 19 Mar 2014 12:03:35 GMT

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

Re: Double Counting in Straight Table Formulae

Anonymous — Wed, 19 Mar 2014 12:39:13 GMT

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

Re: Re: Double Counting in Straight Table Formulae

rustyfishbones — Wed, 19 Mar 2014 12:43:43 GMT

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

Then select attach

Re: Double Counting in Straight Table Formulae

Anonymous — Wed, 19 Mar 2014 12:49:26 GMT

Great thanks.

Re: Double Counting in Straight Table Formulae

rustyfishbones — Wed, 19 Mar 2014 12:54:01 GMT

your welcome!

Re: Re: Double Counting in Straight Table Formulae

Anonymous — Wed, 19 Mar 2014 12:56:47 GMT

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.

Re: Double Counting in Straight Table Formulae

jonathandienst — Wed, 19 Mar 2014 14:05:54 GMT

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.

Re: Double Counting in Straight Table Formulae

Anonymous — Wed, 19 Mar 2014 14:41:03 GMT

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

Re: Re: Double Counting in Straight Table Formulae

Anonymous — Wed, 19 Mar 2014 15:27:33 GMT

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