need help with running total

[ncchish]

I have a report and I need to show a listing of producers for a period of a year before it starts over. I have that date criteria in the record selector. For producers on that list I need to show their sales for the first 6 months of their starting date with the company. That start date can fall anywhere within the year specified by the record selection. I set up a running total to sum their sales and a formula to evaluate the start date and reset it by the group tax id nbr. The formula I’m using for this is DateAdd ("m",6,{Start_Date}) because I want to accumulate sales for only the first 6 months since their start date. This amount will stay on the report until it starts over the next year. I’m getting no data for the running total using this formula.

Any help would be appreciated. I"m using Crystal vers 10.

Thanks

 

[lbass]

You should specify your group structure and provide your record selection formula.

If the report is grouped by tax ID (is this the ID for the producer?), then a running total evaluation formula should be like:

{table.salesdate} in {table.startdate} to dateadd("m",6,{table.startdate})

Reset on change of taxID.

-LB

 

[ncchish]

Thanks LB for looking at this. My results are much closer now but I’m still picking up some unwanted records. For the record selector I have this:
if currentdate > cdate(year(currentdate),7,31) then
(
{Field.Start_Date} > cDate(year(currentdate),7,31)
and
{Field.Start_Date} <= Date(year(currentdate)+1,07,31)
)
else
if currentdate <= cdate(year(currentdate),7,31) then
(
{Field.Start_Date} } > cDate(year(currentdate)-1,7,31)
and
{Field.Start_Date} <= Date(year(currentdate),07,31)
)

and yes, I’m grouping by the producer’s tax id nbr. An example for one producer is his start date is 8/02/05 through 01/02/06 but I’m picking up some extra records after Jan’s date.

Thanks

 

[lbass]

Note that six month's from 8/2/05 is 2/2/06. So do you still think you are getting extra records?

-LB

 

[ncchish]

Yes, the user counts it from from Aug to Jan - really 5 months. The records that are showing up appear after Feb's date.

 

[lbass]

The sales transaction records in the report are not limited--only the start date is. The running total though will only add the sales amounts for dates within the period that applies to the particular producer. There is nothing obviously incorrect with what you have shown us so far.

I think you should show some sample data at the detail level for a couple of producers, and show the running total as it calculates. In other words, place the running total in the detail section and observe what is going on. It is possible that you have some kind of row inflation that is inflating the result.

-LB

 

[ncchish]

I don't see anything wrong either but it's picking up dates outside of the range. Is there a way to change the evaulate formula of the RT to check the Start_date and End_date for the range?

Thanks

 

[lbass]

You have two different date fields--the start date and the sales date. You have limited the records in the report to what looks like a fiscal year for start dates, and then the running total is limited to sales dates that are within the six month period from the start date per the particular producer. This means that if the start date is 7/31/06, that the sales dates will be from 7/31/06 to 1/31/07 for that producer. Isn't that what you want? Please clarify, and also please supply sample data as requested earlier.

-LB

 

[ncchish]

The results I'm getting are correct. I verified this with our analyst. Some of the producers have more than one number assigned to them. This producer is one of them so he has more production that shouldn't show under that number because the date falls out of the range although the activity date (another field) makes it look like it's falling in the range. I appreciate all of your help and your formula is what helped get this working.

Thanks so much!

 

[mparshley]

This is what is happening.

Customer 1 Reagents (Running total of installs) 2
(Total dollars) 2000.
(Avg. per installation) 1000.

Plates (Running total of installs) 2
(Total dollars) 3000.
(Avg. per installation) 1500.

Total (Running total of installs) 4
(Total dollars) 5000.
(Avg. per installation) 1250.

The totals for the customer should be:

(Running total of Installs) 2
(Total dollars) 5000.
(Avg. per installation) 2500.

I've tried using the running total formula field {#installs}
in the running total summary ...suppress->x+2 :

whileprintingrecords;
numbervar installs:= {#Installs};
totext(installs,0)

but the answer is always 1. The calculation of avg per install takes into account whatever figure is in the first summary and works perfectly.

I hope this makes it clearer.

Thank you.

 

[mparshley]

Sorry
I added this to the wrong thread. It probably doesn't make any sense to you.