asked on

Best way to average

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.

peter57r

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

Pete

ThomasBallardIT

ASKER

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

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ThomasBallardIT

ASKER

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

Mike McCracken

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

ThomasBallardIT

ASKER

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

Mike McCracken

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

mlmcc

ThomasBallardIT

ASKER

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

Mike McCracken

Glad i could help

mlmcc