• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

cross tab avg error

My crosstab looks like this

                          2006                   2007
101gsf              123,502.90      123,834.73           (pounds)
                         133,834.51       139,037.53             (sales $)
                            $1.42                     $1.38            (avg    sales / pounds)

Why is the avg calculating wrong?

The formula to find the average is
if {@Pounds Per Order} <> 0 then
{@Total Sales per order} / {@Pounds Per Order}

0
ThomasBallardIT
Asked:
ThomasBallardIT
  • 4
  • 3
1 Solution
 
mlmccCommented:
What are the other formulas?

mlmcc
0
 
ThomasBallardITAuthor Commented:
Pounds per order
if {SorDetail.MStockUnitMass} <> 0 then
if {SorDetail.MOrderUom} = "PK" then
{SorDetail.MOrderQty}/{SorDetail.MConvFactAlloc}*{SorDetail.MStockUnitMass}
else if {SorDetail.MOrderUom} = "LB" then
{SorDetail.MOrderQty}*{SorDetail.MConvFactAlloc}/({SorDetail.MStockUnitMass}  )
else {SorDetail.MOrderQty}*{SorDetail.MConvFactAlloc}*{SorDetail.MStockUnitMass}

Total sales per order
{SorDetail.MOrderQty} * {SorDetail.MPrice}
0
 
mlmccCommented:
What values are in the cross tab?

mlmcc
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
frodomanCommented:
Potentially there are some null values in pounds per order that are throwing off the calculations?  If I'm reading your formula correctly it'll return null if MStockUnitMass = 0.  Perhaps if you add "else 0" to the end of your formula?

frodoman

0
 
ThomasBallardITAuthor Commented:
I added if greater then 0 at the beginning and else 0 at the end.  Still the avg is too high.
0
 
ThomasBallardITAuthor Commented:
0
 
mlmccCommented:
The problem is you are finding the average price for each order then averaging them
For the first item in your sample
6 orders
30 cs of 12 1lb chubs @12/case  360lbs  $360.00        $1.00/lb
12 cs of 12 1lb chubs @12/case  144lbs  $144.00        $1.00/lb
15 cs of 12 1lb chubs @12/case  180lb    $180.00        $1.00/lb
24 lb                           @1.57/lb    24lb      $ 37.68        $1.57/lb
24 lb                           @1.57/lb    24lb      $ 37.68        $1.57/lb
24 lb                           @1.74/lb    24lb      $ 41.76        $1.74/lb
                                                    756lb    $801.12        $7.88 / 6 orders = 1.31

I fixed the problem I think.
I added a group on your stck code and description like you breakout the cross tab
I added summaries (total pound and sales)
I added a formula (called it X) to get average for the group then put it in the cross tab and displayed as an average

Uploaded file
https://filedb.experts-exchange.com/incoming/ee-stuff/5105-Compare-years-report-test_fixed.zip

mlmcc
0
 
ThomasBallardITAuthor Commented:
Geez, thanks for all the help there.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now