Field doubling amount when calculating

[jackie1948]

i have a report that has a break that produces a summary line and on this line i have 3 calculated fields. the results are correct on the report except for 2 different account numbers and on these the amounts are doubled on all 3 calculated fields. I have checked and rechecked the data in the file and there is not any duplicates. My question is has anyone got any suggestions on how to solve this problem.

 

[DoubleD]

If you're grouping in your report, the duplicate records would not show up.
Try adding "DISTINCT" to your calculation to test this.
eg: Total(DISTINCT Rev_Amt)

I am what I am based on the decisions I have made.

DoubleD

 

[jackie1948]

DoubleD
Thanks I do not know why I have not thought about using distinct as I have used it a thousand times before. That worked for the first 2 calculation but on the last one I still need to work on. But atleast you pointed me in the right direction to fix this problem.
again a Big Thanks

 

[griffindm]

Sounds like you have another table joined to your main fact table that has multiple rows for the data in question. Can you post the SQL the report is generating here so we can look at it?

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[the6campbells]

Without examples it hard to know what you have done. If you are using CRN you can review at the cube or tabular query level the logical SQL and native SQL. In the logical queries you will observe for aggregations a notation that defines what partition of data an aggregate is computed over and the set of unique column values at which the aggregation is computed with the partition. This avoids 'double' counting which happens when relationships cause multiple rows to be generated etc.

Typically, you would not explicitly specify the for-clause on an aggregate expression and leave the engine to determine the context but you do so if you want.

Either way.. without context hard to specifically what your root cause and appropriate solution is.