Former Member

# Find Prior Month Sales Per Salesman?

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?

10|10000 characters needed characters exceeded

### Related questions

Posted on Aug 25, 2011 at 11:01 PM

Hi,

A couple of ways you can do this:

1) You can group on ARInvoice.ShipToState then on {ARSalesRep.Name}.

2) Create 4 formulas and drop them into the Detail section:

YTD 2010

```If {ARInvoice.InvoiceDate} In [Date ((Year (Today) -1), 1, 1) to DateAdd (yyyy, -1, Today)] Then
{ARInvoice.Invoicetotal}
Else 0;```

YTD2011

```If {ARInvoice.InvoiceDate} In [Date ((Year (Today)), 1, 1) to Today] Then
{ARInvoice.Invoicetotal}
Else 0;```

July 2010

```If Year ({ARInvoice.InvoiceDate}) = (Year (Today) -1) And
Month ({ARInvoice.InvoiceDate}) = Month (Today) Then
{ARInvoice.Invoicetotal}
Else 0;```

July 2011

```If {ARInvoice.InvoiceDate} In MonthToDate Then
{ARInvoice.Invoicetotal}
Else 0;```

3) Right-click these fields and Insert Summary. Drop the summarized fields into the {ARSalesRep.Name} Group Footer. You can hide the Detail section if you don't need to see it.

4) Create a formula to calcuate the YTD difference using the summarized fields.

The other way would be to create a Crosstab report using the same formulas. All depends on how you want to view your data.

Good luck,

Brian