We help IT Professionals succeed at work.

# Best way to average

on
Medium Priority
344 Views
Crystal Reports XI

I have a report that goes from month to date.  There is a field I need to average, so how would I average quantity sold.
Example
Its October 12th
Oct 1 to 7th - 20 sold
Oct 8 to 12th - 10 sold
So the average should be 15 a week.
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT

Commented:
Hi ThomasBallardIT,
How are you defining a week?
1-7, 8-14, 15-21 etc?
or some other way?

Pete

Commented:
Yes I would be defining a week as 7 days.  The way it is setup now, is that I pick a date and use the "month to date" thingamuhbob.  So I could pick Oct 2nd or Oct 29th or any day and I need it to be averaged in that manner.

Example
If I picked oct 2nd   - 5 sold for the month     Average would be 5
If I picked Oct 25th  - 90 sold for the month   Average would be 22
If I picked Oct 15th  - 50 sold for the month.  Average would be 25
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
How do you define the week?

In case 3 - Oct 15 you have 2 weeks
In case 2 - Oct 25 you have 4 weeks

I think you need to ise some kind of test like

IF (Day({?YourDate}) <= 10) then
TotalSold
ELSE IF (Day({?YourDate}) <= 17) then
TotalSold \ 2
ELSE IF (Day({?YourDate}) <= 24) then
TotalSold \ 3
ELSE
TotalSold \ 4

mlmcc

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
This is what I got but I need a date-time field?

if {InvMovements.EntryDate} <= 8 then {InvWarehouse.MtdQtySold}
else
if  {InvMovements.EntryDate} <=15 then {InvWarehouse.MtdQtySold} / 2
else
if  {InvMovements.EntryDate} <=23 then {InvWarehouse.MtdQtySold} / 3
else
{InvWarehouse.MtdQtySold} / 4
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
The DAY function will pull the Day value out of the DateTime field.

if  Day({InvMovements.EntryDate}) <= 8 then {InvWarehouse.MtdQtySold}
else
if  Day({InvMovements.EntryDate}) <=15 then {InvWarehouse.MtdQtySold} / 2
else
if  Day({InvMovements.EntryDate}) <=23 then {InvWarehouse.MtdQtySold} / 3
else
{InvWarehouse.MtdQtySold} / 4

mlmcc

Commented:
It appears to be working for the most part, but there a few not dividing by anything when in this case they should all be dividing by 4.

if  Day({InvWarehouse.DateLastPurchase}) <= 8 then {InvWarehouse.MtdQtySold} / 1
else
if  Day({InvWarehouse.DateLastPurchase}) <=15 then {InvWarehouse.MtdQtySold} / 2
else
if  Day({InvWarehouse.DateLastPurchase}) <=23 then {InvWarehouse.MtdQtySold} / 3
else
{InvWarehouse.MtdQtySold} / 4
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Could you have some NULL dates?  or bad date fields?

mlmcc

Commented:
I figured it out

if day(maximum({InvWarehouse.DateLastPurchase})) <= 8 then {InvWarehouse.MtdQtySold} / 1
else
if  day(maximum({InvWarehouse.DateLastPurchase})) <=15 then {InvWarehouse.MtdQtySold} / 2
else
if  day(maximum({InvWarehouse.DateLastPurchase})) <=23 then {InvWarehouse.MtdQtySold} / 3
else
{InvWarehouse.MtdQtySold} / 4

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:

mlmcc
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile