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
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.
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
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.
Ok cool...yeah this makes sense. Thank you for your help. Just starting learning this tool and am liking it so far!
Thanks again