We help IT Professionals succeed at work.

Best way to average

ThomasBallardIT
on
Medium Priority
344 Views
Last Modified: 2008-03-10
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

CERTIFIED EXPERT

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

Pete

Author

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.

Ask the Experts

Author

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
Mike McCrackenSenior 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

Author

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
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc

Author

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

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Glad i could help

mlmcc
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.