Bug Tracker Incident #8321

 


Summary
Project
INCDT - xTuple ERP
Incident Category
Bugs
Incident Number
8321
Visibility
public


Product Version
3.2.0
Fixed In Version
3.3.0Alpha


Summary
Summarized Sales By Customer By Item - Average Price Calculation is Wrong


Description

The "Ave Price" column in the "Summarized Sales By Customer By Item" report uses the aggregate AVG() function.
"An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows."

baseunitprice|cohist_qtyshipped|baseextprice
-------------+-----------------+------------
2.00_________|10_______________|20.00
8.00_________|1________________|8.00

AVE(baseunitprice) = 5.00 <- Incorrect Average Price
(SUM(baseextprice) / SUM(cohist_qtyshipped)) = 2.5454 <- Real Average Price

http://postbooks.svn.sourceforge.net/viewvc/postbooks/xtuple/trunk/guiclient/dspSummarizedSalesByCustomerByItem.cpp?revision=5078&view=markup

Line 122:
- "AVG(baseunitprice) AS avgprice, SUM(cohist_qtyshipped) AS totalunits,"

+ "CASE WHEN SUM(cohist_qtyshipped) = 0"
+ " THEN 0"
+ " ELSE (SUM(baseextprice)/SUM(cohist_qtyshipped))"
+ " END AS avgprice,"
+ "SUM(cohist_qtyshipped) AS totalunits,"


Details
Reporter
bendiy
CRM Account
Xikar, Inc
Status
Closed
Assigned
reddog


Priority
Normal
Severity
Patch
Updated
03-15-11 14:46
Resolution
Fixed



Comments

DateUsernameComment
03/04/11 08:25mfgadmin

The "Ave Price" column in the "Summarized Sales By Customer By Item" report uses the aggregate AVG() function.
"An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows."

baseunitprice|cohist_qtyshipped|baseextprice
-------------+-----------------+------------
2.00_________|10_______________|20.00
8.00_________|1________________|8.00

AVE(baseunitprice) = 5.00 <- Incorrect Average Price
(SUM(baseextprice) / SUM(cohist_qtyshipped)) = 2.5454 <- Real Average Price

http://postbooks.svn.sourceforge.net/viewvc/postbooks/xtuple/trunk/guicl...

Line 122:
- "AVG(baseunitprice) AS avgprice, SUM(cohist_qtyshipped) AS totalunits,"

+ "CASE WHEN SUM(cohist_qtyshipped) = 0"
+ " THEN 0"
+ " ELSE (SUM(baseextprice)/SUM(cohist_qtyshipped))"
+ " END AS avgprice,"
+ "SUM(cohist_qtyshipped) AS totalunits,"

08/07/09 14:06svn

Revision: 6740
http://postbooks.svn.sourceforge.net/postbooks/?rev=6740&view=rev
Author: techdoc
Date: 2009-08-07 18:06:33 +0000 (Fri, 07 Aug 2009)

Log Message:
-----------
docs for issue #8321

Modified Paths:
--------------
xtupledocs/trunk/referenceguide/guiclient/dspSummarizedSalesByCustomerByItem.xml
xtupledocs/trunk/referenceguide/guiclient/dspSummarizedSalesByCustomerType.xml
xtupledocs/trunk/referenceguide/guiclient/dspSummarizedSalesByCustomerTypeByItem.xml
xtupledocs/trunk/referenceguide/guiclient/images/dspSummarizedSalesByCustomerByItem.png
xtupledocs/trunk/referenceguide/guiclient/images/dspSummarizedSalesByCustomerType.png
xtupledocs/trunk/referenceguide/guiclient/images/dspSummarizedSalesByCustomerTypeByItem.png

01/31/09 14:24reddog

Change Summarized Sales by Customer Type, Summarized Sales by Customer Type by Item, and Summarized Sales by Customer by Item and add a new column to listview - Weighted Average Price. Calculate Wt. Avg Price as described by requestor - SUM(baseextprice) / SUM(cohist_qtyshipped).

01/31/09 14:21svn

Revision: 5136
http://postbooks.svn.sourceforge.net/postbooks/?rev=5136&view=rev
Author: sunsetsolutions
Date: 2009-01-31 19:21:33 +0000 (Sat, 31 Jan 2009)

Log Message:
-----------
Issue #8321:add weighted average price column

Modified Paths:
--------------
xtuple/trunk/guiclient/dspSummarizedSalesByCustomerByItem.cpp
xtuple/trunk/guiclient/dspSummarizedSalesByCustomerType.cpp
xtuple/trunk/guiclient/dspSummarizedSalesByCustomerTypeByItem.cpp

01/30/09 17:21xikar

This calculation is used in other places:
1. Summarized Sales By Customer Type
2. Summarized Sales By Customer Type By Item
3. Report Queries



Characteristics

Backport
No
Operating System
 
Doc Flag
False
Copyright Assigned
Yes
HaxTuple
No

Files

No Files

Related Documents

TypeNumberDescriptionRelationshipRemove
ProjectXTUPLEAPPSPorted From Mantisx


Subscribers

You do not have permission to view subscribers.


Incident History

DateUsernameFieldChange
12/16/10 09:13acdrupalNewIncident Added

 

mead