Total of all orders- date formula 1

[hencyt]

I have an Open order Report that shows the total $ on order by customer, by month. It dynamically changes according to the current date (i.e. if the month is Nov, then it shows 12 months from Nov). However, it is not picking up the orders where the date field (shipexpiredate) has already passed. I want to include all order totals for all previous months in the current (1st) month total. Here is an example of the formula I am using to get the month totals;
------------------------------------
Local DateVar d := ({SO1_SOEntryHeader.ShipExpireDate});
local datevar g := (currentdate) ;
Local DateVar Range dr0;
dr0 := DateSerial (Year(g), Month(g),1 - 1 ) To
DateSerial (Year(g), Month(g) + 1, 1 - 1);

if d in dr0 then {@Total Open Orders}
-----------------------------------
The report looks like this:

customer name month1 month2 month3 month4
Retail Store 300.00 240.00 20.00 95.00
Retail Store2 0.00 25.00 30.00 15.00
Total all cust --------------------------
300.00 265.00 50.0 110.00

Month1= the month of the current date. Month2-all orders whose shipexpiredate in next month, etc.

How do I need to change my date formula for the first month to get order totals for all orders whose shipexpiredate was in current month or any month previous?
------------
Question - 2 (Am I allowed 2 questions?)

I need to change the date formula to accomodate for year changes. So if I run it today for November, when the report is figuring Month3 it will pull Jan2004 info and not Jan2003.

Thanks in advance,
Sunny

 

[hencyt]

I don't know what I was thinking. Here is what I came up with for the first formula I was asking about. I was making it harder than it was. This formula seems to get me what I need:
-------------------------------
Local DateVar d := ({SO1_SOEntryHeader.ShipExpireDate});
local datevar g := (currentdate) ;
Local DateVar Range dr0;
dr0 := DateSerial (Year(g),1,1 ) To
DateSerial (Year(g), Month(g), 1-1 );

if d in dr0 then {@Total Open Orders}
---------------------------------


But I still need help on the second question please!

Sunny
But I would still

 

[hencyt]

Nope- that formula does not work for finding totals in any previous years. Only in the current year, previous months. So if I run the report for Jan. 2004- it will not find any of the shipexpiredates in 2003.

I guess I need help on both questions then.

Sorry for the multiple posts. I hope someone can follow my train of thought.

Any help appreciated.

Sunny

 

[bdreed35]

What version of Crystal are you using? Looking at the formula you posted, it looks like that is just one of the formulas you are using. I think you need 12, one for each Month total. It also would be nice to see what you are doing in the {@Total Open Orders} formula.

You need to create 12 different formulas that will evaluate at the detail level
@Month1

if DateDiff(&quot;m&quot;,CurrentDate,{SO1_SOEntryHeader.ShipExpireDate}) <= 0 then
    1
else
    0

@Month2

if DateDiff("m",{SO1_SOEntryHeader.ShipExpireDate}) = 1 then
    1
else
    0

.
.
.
@Month12

if DateDiff("m",{SO1_SOEntryHeader.ShipExpireDate}) = 11 then
    1
else
    0

Now you can create summaries off each of these for your Customer Group.
Insert, Summary then sum,Month1,customer group.
Insert, Summary then sum,Month2,customer group.
.
.
.
Insert, Summary then sum,Month12,customer group.

~Brian

 

[bdreed35]

Forgot to add that this solution should solve both of your questions.

~Brian

 

[hencyt]

Thanks Brian.
Crystal 8.5 SQL db.

Yes that is just one of the formulas- there is a diferent one for each month- following the pattern of the one I included. I should have included that info.
------------------------
{@Total Open Orders}
{SO1_SOEntryHeader.DiscountAmount}+{SO1_SOEntryHeader.TaxableAmount}+{SO1_SOEntryHeader.NonTaxableAmount}+{SO1_SOEntryHeader.SalesTaxAmount}+{SO1_SOEntryHeader.FrghtAmount}
-------------------------

Though I am not sure why that matters- my order totals by month are correct, it is just the date range I am having trouble specifying--- when I move from year to year.

Thanks for your suggestion- I will try it.

Sunny

 

[bdreed35]

Ok. You can change each of the true conditions of the if statements to return {@Total Open Orders} rather than 1
@Month1

if DateDiff(&quot;m&quot;,CurrentDate,{SO1_SOEntryHeader.ShipExpireDate}) <= 0 then
    {@Total Open Orders}
else
    0

Repeat for each of the 12 formulas.

~Brian

 

[hencyt]

Brian,

THANKS! It works great. That was a much easier route than I was trying to take.


Sunny