Crystal Reports Find Prior Month Sales Per Salesman?

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Find Prior Month Sales Per Salesman?

Author	Message
Repent34 Newbie Joined: 03 Mar 2011 Location: United States Online Status: Offline Posts: 19	Topic: Find Prior Month Sales Per Salesman? Posted: 25 Aug 2011 at 9:06am
	I have salesmen throughout the country. Each salesman sells in a particular state. I want to total up each salesman's invoice sales for the prior month (I will also do this for: July 2010, %Difference between July 2011/July2010, YTD 2010, YTD2011, and percent difference between YTD2010 and YTD2011) The tables.fields I have are: ARSalesRep.Name ARInvoice.InvoiceDate ARInvoice.Invoicetotal ARInvoice.ShipToState How would I construct formulas for these?
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 25 Aug 2011 at 9:22am
	you an use a running total with an evaluation formula ARInvoice.InvoiceDate in lastfullmonth
	IP Logged

Repent34 Newbie Joined: 03 Mar 2011 Location: United States Online Status: Offline Posts: 19	Posted: 25 Aug 2011 at 11:07am
	*Originally posted by DBlank* you an use a running total with an evaluation formula ARInvoice.InvoiceDate in lastfullmonth The way they want the form laid out is almost like an excel spreadsheet in that at the top of the form they want "column" headers July2011, July2010, %Difference, YTD2011, YTD2010, %Difference. On each "row" will be the salesman's name. Would I just create the report by hide or supress drill-down so that there is basically one line per salesman?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 25 Aug 2011 at 11:25am
	here is one way... (note:You cannot use a traditional grouping or crosstab here because you have one row data that needs to be counted in more than one grouping.) group on the salesman name. Suppress the group header and details. place the salesman name in the group footer create running totals to evaluate eacjh of your month year needs and palce these in the group footer as your 'columns'. You can then also use the RTs in formulas to get your %differences (also palced in the group footer). RT samples: name=Lastmonth field to summarize= invoicetotal type=sum evaluate = use a formula ARInvoice.InvoiceDate in lastfullmonth reset= on change of group (select sales person group) name=LastmonthLastYear field to summarize= invoicetotal type=sum evaluate = use a formula year(ARInvoice.InvoiceDate) in dateserial(year(currentdate)-1,month(currentdate)-1,1) to dateserial(year(currentdate)-1,month(currentdate),1-1) reset= on change of group (select sales person group) formula sample is {#Lastmonth}%{#LastmonthLastYear} Edited by DBlank - 25 Aug 2011 at 11:25am
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum