Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

sinergi1234
New Contributor II

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

Re: Double Counting in Straight Table Formulae

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

Re: Double Counting in Straight Table Formulae

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

sinergi1234
New Contributor II

Re: Double Counting in Straight Table Formulae

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

rustyfishbones
Honored Contributor II

Re: Re: Double Counting in Straight Table Formulae

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

sinergi1234
New Contributor II

Re: Double Counting in Straight Table Formulae

Great thanks.

rustyfishbones
Honored Contributor II

Re: Double Counting in Straight Table Formulae

your welcome!

sinergi1234
New Contributor II

Re: Re: Double Counting in Straight Table Formulae

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.

MVP
MVP

Re: Double Counting in Straight Table Formulae

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
sinergi1234
New Contributor II

Re: Double Counting in Straight Table Formulae

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

sinergi1234
New Contributor II

Re: Re: Double Counting in Straight Table Formulae

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