3 Replies Latest reply on Nov 12, 2014 11:08 AM by ChadBarnard

    Charting Sales for the Month by Salesperson Depending on Job Status

    ChadBarnard

      Title

      Charting Sales for the Month by Salesperson Depending on Job Status

      Post

      I know there are a lot of posts about this topic, but I've read through them and I'm still lost.

      I have home page layouts for all of my departments.  When they log in to Filemaker, they are directed to their specific layout, which has a header w/ navigation buttons, and is sorted (with a sub summary field part) by "Current Status".

      This is to show them all of the jobs that are in their queue, and what action needs to be performed next.

      For the executive home page layout, I'm wanting to place some charts in the header.  One will be sales for the month by salesperson depending on "Job Status".  In my database I have the following job statuses: Inactive, Active, Complete, Dead.  I want the chart to reflect all jobs that are either "Active" or "Complete" broken up by the salesperson.

      I've looked all around the web, looked through the tutorials, and have got the basic charts (i.e. invoice summary) down, but I haven't figured out how to accomplish what I'm trying to achieve.

      The "Salesperson" and "Job Status" fields are populated by value lists and are on the "Orders" table.

      Thanks for any help.

        • 1. Re: Charting Sales for the Month by Salesperson Depending on Job Status
          philmodjunk

          What describe is a chart that would require to "Y axis" data series with the data drawn from groups of records grouped by Month. That could be a stacked bar chart or a line chart with two lines, to name two possible charts. You might chart the number of orders each month or the total sales amount each month for each job status.

          One way to do this is to base your layout on orders and define two calculation fields:

          cActiveSalesAmt:

          If ( Job Status = "Active" ; SalesAmt )

          cCompleteSalesAmt:

          If ( JobStatus - "Complete" ; SalesAmt )

          You can then define two summary fields, one to total each of these added calculation fields.

          You can then use the summary fields as the source of data for the two Y-axis data series and sort your records to group them by month. Make sure that your data source options specify the "summarized data" option.

          • 2. Re: Charting Sales for the Month by Salesperson Depending on Job Status
            ChadBarnard

            What if I wanted to count the amount of jobs, not the money amount sold?  In my case, we have contracted jobs (which have a total dollar amount) and time & material jobs (which, until they're billed, have no total dollar amount).  I'd like to show how many jobs, out of the total amount of jobs, the salesperson has sold for the month.

            Would I need to create a field that just counts each job and then reference that where you have "SalesAmt"?

            • 3. Re: Charting Sales for the Month by Salesperson Depending on Job Status
              ChadBarnard

              On the x-axis, instead of displaying each salesperson once, it is displaying their name for each job.  As you would expect, this returns a garbled mess of a line.