Date range formula help

[stevens157]

I have a list of dates that show order dates. I want to figure out the average ordered per month for the whole range instead of just for months with orders. meaning if i had the following dates and orders this is what I want to get.

01/01/02 5
03/01/02 5
06/01/02 5

the average per month with crystal is 5, what i need to get is the average for the whole range of six months 2.5. any ideas?

 

[synapsevampire]

I fear that you'll have to manually count the months between the lowest date and the highest date, and maintain a sum of avg ordered per month values, then do the average yourself.

To get the months, you can use a simple means, which is running total fields to take the minimum and maximum of the dates.

Now use datediff('m',RTmin, RTmax)

Now do the math:

(datediff('m',RTmin, RTmax) / sum(MyAvgMonthValues)) *100

There's one way anyway, there are a few.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[stevens157]

ok call me simple but I cannot seem to make the first formula work. Where do I enter the field I want converted?

 

[synapsevampire]

Sorry, I was showing a percent, and incorrectly, I was very tired this morning...

What I was suggesting is that you create 2 running total (RT) fields which get the minimum and maximum dates for your date field (I'll call them RTmin and RTmax). You'll also want a running total field which contains the sum of your values I'll call it RTsumvalue).

Now that you have these, you can determine the number of months using the datediff function on the 2 RT date fields in a new formula, datediff returns the difference between 2 dates, in thsi case we ask for months.

Use Insert->Field Object to create formulas or RT's, and create a new formula to hold the final formula, which is:

sum(MyAvgMonthValues) / datediff('m',RTmin, RTmax)

This formula will go wherever it's need, perhaps the report footer. It's unfair to ask me where to place this when you haven't described where you've placed anything on the report.

So, you show me yours, and I'll show you...

Sorry, been a long day.

And I wouldn't call you simple or anything of the kind, not knowing Crystal well isn't what I consider a *bad* thing most days

You could also create a single formula to do all of this:

sum({YourTable.YourValueField}) / (datediff('m',minimum({YourTable.YourDatefield}), maximum({YourTable.YourYourField})))

I feared that you'd have groups and then we'd have to run through that, I generally explain this sort of thing with Running Totals to differentiate the formulas and leverage built in functionality, especially with graphical tools like the Running Totals.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Oh, and the datediff will return 5 months for 1-1 to 6-1, so you'll need to add a +1 to the end of that.

You may have 1-1 to 1-31, and the datediff will return 0, and 1-31 to 2-1 will return 1, if this isn't what you want, please describe what constitues a month.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com