Find Prior Month Sales Per Salesman

[Repent34]

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?

 

[MCuthill]

Repent34,

You will need a record selection which looks at the start of the prior year to the end of the last full month.

Once the record selection is completed, you will need a formulas for at least:
1) MonthThisYear
2) MonthLastYear
3) YTDThisYear
4) YTDLastYear

The report will need to be structured with {ARSalesRep.Name} as a Group.

The specific criteria/formulas are as follows (and assumes prior month for Current Date - July's if ran in August, etc):
Record Selection:

{ARInvoice.InvoiceDate} in [Date(Year(CurrentDate)-1,1,1) to Date(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate))-1]

{@MonthThisYear}

IF {ARInvoice.InvoiceDate} in LastFullMonth THEN {ARInvoice.Invoicetotal} ELSE 0

{@MonthLastYear}

IF {ARInvoice.InvoiceDate} in [Date(Year(CurrentDate)-1,Month(CurrentDate)-1,1) To Date(Year(CurrentDate)-1,Month(CurrentDate),1)-1] THEN {ARInvoice.Invoicetotal} ELSE 0

{@YTDThisYear}

IF {ARInvoice.InvoiceDate} in YearToDate THEN {ARInvoice.Invoicetotal} ELSE 0

{@YTDLastYear}

IF {ARInvoice.InvoiceDate} in LastYearYTD THEN {ARInvoice.Invoicetotal} ELSE 0

Create summaries for all the above, on the {Name} group. Once the summaries are created, create formulas referencing summaries to complete your % Change requirements as needed.

Cheers! Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."