Compare sales for last year with current year by month by salesperson?

  • 1
  • 1
  • Question
  • Updated 7 years ago
  • Answered
Report wants:

I would like to make a report that shows all current year sales by month vertically then after every current month in the report, so last years. Where the sales person is the horizontal line. A summarized style would be nice for the drill down affect. I.E.

Jan(This Year) | Jan (Last Year) | Feb (This Year) | Feb (Last Year)
SalesmanA
SalesmanB

Only have 1 table with every record based on the sale:

Salesperson
Date of sale
sale amounts
etc..


I'm not adverse to adding a summary table, but know very little about how to do so. Nor would I mind just adding addition fields to the record. Any help would be greatly appreciated. If I can make this work I can get a lot of other reports for other departments out of spread sheets and into quickbase!
Photo of bvanderp

bvanderp

  • 0 Points

Posted 7 years ago

  • 1
  • 1
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
You may be able to get this effect by creating a text field called perhaps [MMYY] in the format MMYY (with the months being 01, 02, 03) etc, for each sales record.
The formula would be

// create the Month but zero pad with leading zeros to 2 characters.
var text Month = Right("0" & Month([date]),2);
// create the relative year indicator, either Current Year or Last Year.var text CYLY = If( Year(Today())= Year([date]),"CY",Year(Today())-1 = Year([date]),"LY");
// put them together$Month & $CYLY

So have your summary repport be a total of the sales, and your rows are Sales Reps and your columns are this new MMYY field.
Mark