1 Reply Latest reply on Jun 2, 2011 2:19 PM by philmodjunk

    Help using data to analyse sales



      Help using data to analyse sales


      I own a furniture store and am building a program to make and track sales of inventroy items.  In a nut shell I have Customers, Products, Invoices and line Items tables.

      What I am trying to do is analyse my sales to track sales trends mainly via graphs.  Meaning having a graph that shows the number of sales of the different colored furniture over a given period of time. Also a graph of sales by date and or time of day.  And more but I figure if I can figure out how to make one it should be similar for different criteria.

      My thought has been so far that I have to use the line Item table as the source for this data.  What I am trying to figure is how to say, ok here is a list of all of my sales and I want a multiline line graph showing the number of sales of each of the colors over the last year. In the line item table I have a lookup field that enters say "Red" which it gets from the product table. I figure I need to do some type of count and summary to show that there were 10 "Red" 7 "Tan" and 11 "Brown" pieces sold last month.  How do you recommend I set that up?

      Basically the same questions here just different criteria.  I want to graph sales by Date (1/1/2011), Day of Week (Thursday), and Time of day (12:30pm) and any combination of the three.  Any thoughts on this one?

      Thanks in advance,


        • 1. Re: Help using data to analyse sales

          First, the basics:

          Define a summary field as the "count of" some field in your lineitems table that is never empty such as a Product ID field.

          Set up your chart object to use the color field as the Horizontal X axis. Use this summary field as the Y axis. Then select the Use data from current found set and show data points for groups of records when sorted options.

          Put this chart object in the body of your layout and sort your records by the color field. You can limit what data is used in the chart by using a Find to pull up just the records you want, such as just the records for a specified date range or only specific products sold.

          If you want to see multiple charts--one for each day of the week or each month, you can this same chart in a sub summary part that sorts by a field that will group your records by week or month. This may require adding a calculation field that allows you to group records into those categories when you sort the records.