I would like to be able to compare our customer's sales LY and YTD so far. Perhaps even the year before...we are running GridView, AR, Sales Analysis. What is the best way to go about this? Crystal Reports is also an option.

Thanks very much.

Hi Barb,

Using GridView on your Sales Analysis transactions database is probably the best way to go.

Create calculated columns to list the LY, YTD or other sales amount date range. Ensure that you choose to include a sum for each calculated column, and set a sort order by the customer code. When you summarize the view, you will get one row for each customer and the calculated columns will display totals for LY, YTD etc for each customer.

To create a YTD calculated column, use syntax something like:
IF(
AND(
{Date} >= BEGINYEAR( )
,
{Date} <= TODAY( )
)
,
{Amt}
,
0
)

To create a full Last Year calculated column, use syntax something like:
IF ( YEAR( {Date} ) = Year( TODAY( ) ) -1
,{Amt}
,0)


Syntax for two years ago is similar:
IF ( YEAR( {Date} ) = Year( TODAY( ) ) -2
,{Amt}
,0)


It gets a little more complicated if you want Last Year to Date as opposed to the full year:
IF(
AND(
{Date} >= EDATE(BEGINYEAR(),-12)
,
{Date} <= EDATE(TODAY(),-12)
)
,{Amt}
,0)

where the EDATE function gives you the date a certain number of months in the future or in the past. -12 means the same date 12 months in the past.

If you are interested in Invoice Totals for YTD and LY they are carried in the Customer Master.

Using Smart Finder from the Customer Grid and Excel Direct you can get a quick approximation.

Choose only those Customers where Inv Total - LY or Inv Total - YTD are Not equal to 0

If you want a 3 year comparison you will need to use GridView and Sales Analysis

Thank you very much, we will try this!