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:





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


    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 
    Else 0;


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

    July 2010

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

    July 2011

    If {ARInvoice.InvoiceDate} In MonthToDate Then 
    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,


    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.