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

Average help

Started by NewGuy1983, 22 Aug 2018 09:55:10 AM

Previous topic - Next topic

NewGuy1983

Hello,

I'm new to cognos analytics and just trying to create a simple report. My report is displaying a list and I'm using IBM's sample data.
The report looks like below.

Product Line         Quantity          AVG QTY
Golf Equipment     1,095,214

I'm just trying to calculate the average qty within each product line.

I've tried 'average ([Quantity] For [Product line] )' and this produces the incorrect value. Can someone please tell me what I'm doing wrong. Can't find much by doing a simple google search.

Thanks

MFGF

Quote from: NewGuy1983 on 22 Aug 2018 09:55:10 AM
Hello,

I'm new to cognos analytics and just trying to create a simple report. My report is displaying a list and I'm using IBM's sample data.
The report looks like below.

Product Line         Quantity          AVG QTY
Golf Equipment     1,095,214

I'm just trying to calculate the average qty within each product line.

I've tried 'average ([Quantity] For [Product line] )' and this produces the incorrect value. Can someone please tell me what I'm doing wrong. Can't find much by doing a simple google search.

Thanks

Hi,

Bring in the Quantity item a second time from the package, and in the Properties pane, change its Detail Aggregation to Average.

Cheers!

MF.
Meep!

NewGuy1983

Thanks for your reply. I tried this and I'm still not receiving the correct result.
My values to make up the 1,095,214 are:

83,281
69,254
662,903
279,776

The average should be 273,804 but the report is displaying 278.68040712

MFGF

Quote from: NewGuy1983 on 22 Aug 2018 11:57:16 AM
Thanks for your reply. I tried this and I'm still not receiving the correct result.
My values to make up the 1,095,214 are:

83,281
69,254
662,903
279,776

The average should be 273,804 but the report is displaying 278.68040712

Hi,

The average is the average of all the detail rows that make up the total for the product line. In the case of the samples, the detail rows are at the order detail level, and there are thousands of these rows for each product line (19,757 for Golf Equipment in my samples database). If you bring in the Order Detail Code you can see this. The accurate average is the total quantity for the product line divided by the number of order detail rows, and this is what the report is doing. Where are you getting the four figures above from? Are they product type quantities? If so, each of these is an average in itself - again based on the underlying detail rows for each product type. Averaging these averages will give you a different result from the real average for the product line...

Cheers!

MF.
Meep!

NewGuy1983

Ok cool...yeah this makes sense. Thank you for your help. Just starting learning this tool and am liking it so far!

Thanks again