Quarterly Sales Figures

November 13, 200817 yr

Newbies

Hello,

I need help from a FileMaker guru here. I am building a FM database for my employer that will act as a CRM database for our global reseller network. I’m relatively new at this, so bear with me.

Everything is working well for the most part, except I am having problems with organizing and displaying sales data for each reseller.

I am trying to show reseller data through a portal, which will look like this. Annual sales, show as a quarterly sum

Year Q1 Q2 Q3 Q4 Total

2008 $100 $200 $300 $400 $1,000

Additionally, I’d like to see a second portal on the same layout, except, rather than quarter results, yearly results by month.

Year Jan Feb March Etc. Total

2008 $100 $200 $300 $400 $1,000

2007 $150 $50 $400 $100 $700

2006 $100 $125 $200 $100 $525

I can’t figure out what to tell FM so that it calculates these various numbers. Here is some basic information on how I have my table’s set-up. Please note, this database has no invoicing, or ordering capabilities, so there are no “transactions” occurring in this database. Rather, we manually enter reseller sales data on a monthly basis, which will be gathered from our primary business system.

Resellers

Company_id

Sales

Sales_date

Company_id (one-to-many relationship with reseller TO)

Month_Amount

(I’ve imported 4 years of sales history from Excel using the field names listed above)

I also have the following fields set-up in the Sales table.

Quarter_sold – as calc = GetValue ( "Q1¶Q2¶Q3¶Q4"; Ceiling (Month ( sales_date ) / 3 ))

Month_sold – as calc = GetValue ( “Jan¶Feb¶March¶April¶May¶June¶July¶August¶Sept¶Oct¶Nov¶Dec"; Ceiling (Month ( sales_date ) /1) )

Year_sold – as calc = Year ( sales_date )

I don’t know if I need these or not???