An easy one: order amounts analysis

[Igus]

Hi all,
I'm trying to make a complete analysis of order amounts in the last X months. I have a table with: 1)order number; 2) order date; 3)code; 4) amount; 5) customer.
What I need is: a) daily amount average for each code; b) standard deviation for each code; c) number of customers for each code.
The problem is: if I group orders daily and then I group them for code I cannot calculate AVG and StdDev because crystal report doesn't consider the days with amount = 0.
Example:
Order date: 1st feb 2000
Order number: xxxx
Code: 1.1.1.1
Amount: 10
---------------------------
Order date: 3rd feb 2000
Order number: xxxx
Code: 1.1.1.1
Amount: 20

Avg should be 10, Crystal calculates 15
StdDev should be 8.16, Crystal calculates 5
What can I do?

 

[kenhamady]

You probably won't be able to use the summary functions, but you can calculate these 'manually' in a formula. The number you need to calculate first is the number of days, since counting them might be short as you have pointed out.

One way is to subtract your begin date from your end date to get the number of days (if you have parameters). Or you can do a Minimum and Maximum to derive them from the data in the report.

What determines the date range? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Igus]

User should determine the date range, through our information system, but I can even use something like "last six months", which is easier.
The problem of doing it manually is not the average, is the standard deviation. I'll try, but I'm not sure. Thank you very much.

 

[kenhamady]

I don't know the formula for STDev but I have heard that it can be replicated in a formula without too much trouble. Someone here may know it. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Igus]

No problem for the formula, I just wanted something easier. Thank you