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.
LVL 3
ThomasBallardITAsked:
Who is Participating?
 
mlmccConnect With a Mentor 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
0
 
peter57rCommented:
Hi ThomasBallardIT,
How are you defining a week?
1-7, 8-14, 15-21 etc?
or some other way?

Pete
0
 
ThomasBallardITAuthor 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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ThomasBallardITAuthor 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
0
 
mlmccCommented:
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
0
 
ThomasBallardITAuthor 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
0
 
mlmccCommented:
Could you have some NULL dates?  or bad date fields?

mlmcc
0
 
ThomasBallardITAuthor 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

0
 
mlmccCommented:
Glad i could help

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.