If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Incorrect aggregation list

Started by Joe2k15, 25 Mar 2015 06:52:11 PM

Previous topic - Next topic

Joe2k15

Hi,

I am fairly new to cognos and this site, have searched around for a solution to this but to no avail.
Hopefully someone can give me some advice here...
I have 1 query which contains for example: Contact,Product,Order Date,National Avg,Qty
I have a list in my report page based on that query excluding the Order Date column as I don't want to see the qty per order in my list, I am actually trying to sum Qty it per contact and product.

National Average = the national average monthly usage per contact for the product so the national avg for each product is always the same in the table, it is pre-calculated in the SQL source data.
The Qty column is the qty per product on each order.
I need to sum all of the qty per product per contact and compare it to the national avg but for some reason cognos wont aggregate it properly.
When I summarize or use Total it seems to sum of the qty of product by contact but it then multiplies if by the number of unique products against the contact.

So for example,

My source data has 2 orders:

Order 1,Apples,10
Order 1,Paper,20
Order 3,Tissue, 30
Order 2,Apples,10
Order 2, Paper, 20
Order 3,Tissue, 20

I get this in my list:

Apples,60
Paper,120
Tissue,150

But I want this:

Apples,20
Paper,40
Tissue,50

It is really odd and I have tried loads of ways to fix it.
The first 3 columns are grouped so I only get one contact, one instance of each product and the national average for that product. If I leave everything on automatic aggregation the first 3 columns group ok but I still get the individual qty's from each order under Qty rather than the total for that contact and product.

Hopefully someone can understand me and help me.

Many thanks in advance, Joe.







Joe2k15

I meant to say 3 orders in my example not 2