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

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
• 4
• 3
1 Solution

Commented:
What are the other formulas?

mlmcc
0

Author 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

Commented:
What values are in the cross tab?

mlmcc
0

Commented:
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

Author Commented:
I added if greater then 0 at the beginning and else 0 at the end.  Still the avg is too high.
0

Author Commented:
0

Commented:
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

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

mlmcc
0

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

Featured Post

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