Skip to Content
author's profile photo Former Member
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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.