12 Replies Latest reply on Jan 31, 2014 12:01 PM by SteveFinch

    Chart with data and dates

    SteveFinch

      Title

      Chart with data and dates

      Post

           Does anyone know how to make a chart that will display the following info:

           I would like this chart to display the:
           Part number (as it does) and the dates "grouped" if it can.
           It already is sorting by date, just not showing the dates on the chart.

           This info comes from a table that has 6 different part numbers that sell each month.

           Example:

           CMD-103  22 units 2/2013
           CMD-103  43 units 2/2013
           CMD-104  80 units 3/2013
           CMD-104  35 units 3/2013
            

           Thanks

           Spike

      sample-chart.jpg

        • 1. Re: Chart with data and dates
          philmodjunk

               Do you have a date field with the needed date in your table? Did you select it for the X-axis values?

               What kind of data is "CMD-104"? Is that the part number?

               Do you need to group your data by part number and by date?

          • 2. Re: Chart with data and dates
            SteveFinch

                 I think the big problem is I am using FMP11 and need to upgrade to 13 to get more options

                 Yes, I do have a date field
                 Yes, The CMD-104" Is a part number.  The data is amounts sold.

                 Yes, I would like to group by date. (it already is doing that, it's just not showing the date text)

            • 3. Re: Chart with data and dates
              philmodjunk

                   While the dialogs have been changed, I don't think that you'll find much in FileMaker 13 to help you for this specific type of bar chart.

                   But are you grouping by date AND by part number?

                   Do you use a summary field to compute totals that are then used as your Y-Axis values?

                   What have you selected in Chart Setup for the X-axis?

              • 4. Re: Chart with data and dates
                SteveFinch

                     X Axis  is Date
                     Y Axis is Qty

                     That's it, just the two That's all I know how to do....
                     I am kind of stupid when it come to this stuff

                     But after I see an example, I can learn... :)

                • 5. Re: Chart with data and dates
                  philmodjunk

                       Well your Date is not a date or it would not have "CMD 104" data showing on your chart. Either that or you have specified a different field for the X axis than the date field.

                       If you had specified a field of type date, we'd see dates across the bottom of this chart.

                  • 6. Re: Chart with data and dates
                    philmodjunk

                         And please explain what you want each bar on the chart to represent if it were to work correctly.

                    • 7. Re: Chart with data and dates
                      SteveFinch

                           Something like this would work out

                           The CMD numbers are just a short description of the product name. 
                           I made the chart using the quanity sold.
                           Filtered by one vendor
                           Sorted by month
                           So it gave me 45 records (6 different products) from 12 different months.

                           So I would love to be able to have a chart that would show how many products sold in each month and dispay the product name as I feel the legend could get ulgy fast.

                            

                           Thanks

                           Spike

                      • 8. Re: Chart with data and dates
                        philmodjunk

                             OK. this is how I currently understand this: You want one bar for each product and the length of the bar represents the total product sold for one month. Thus you need to group your records by both Product AND by month (not the date) in order to get the chart that you describe.

                             I still don't see why your cart shows "CMD 102" type data across the bottom of your chart. That concerns me as it suggests that you either have not set up the chart as you described or you have both  date and the part description in the same field--which then is not a date field.

                             The biggest issue to resolve is how to group your data in order to get the bars that you want. In a summary report, you can perform a find for records for a specific vendor. A sort can then group the records by Month and by Part ID to compute and show subtotals broken down by both month and Part.

                             But FileMaker Charts can only use a "single field" sort for grouping so this requires a bit of creativity to get the needed bars in your chart.

                             Before I go there, please confirm if this is the case for the records in this table:

                             Do you have multiple records with a Qty number field for a given part, month and record or do you have just one record that shows the total parts for one part, vendor and month?

                        • 9. Re: Chart with data and dates
                          SteveFinch

                               "You want one bar for each product and the length of the bar represents the total product sold for one month. Thus you need to group your records by both Product AND by month (not the date) in order to get the chart that you describe."

                               Correct

                                

                               Here is an example of the data in my table (image)

                               Lets assume that the chat I did was simply luck (good or bad)..
                                

                          • 10. Re: Chart with data and dates
                            SteveFinch

                                 So the way I get the info right now is. Once a month I download a file from Amazon on the 13th of the month. That file shows the total sales for that month.  That could change in the future, but will address that at that time.

                            • 11. Re: Chart with data and dates
                              philmodjunk

                                   What I see in your table is that you get one record each month (dated the 13th of the month) for each part. Thus there is no need to sort and group records in order to chart a single bar on the graph for a group of records. It's one record per part per month. That greatly simplifies the details you have to deal with to get this to chart correctly.

                                   In Chart Setup, you can specify a calculation for the X axis. You might, for example, use this calculation:

                                   Month ( YourTable::Date ) & "/" & Year ( YourTable::Date ) & " " & YourTable::SKU

                                   For the Y Axis, you can select the Qty field.

                                   You can click Format Chart... to get to a dialog box where you can rotate the x Axis labels 45 or 90 degrees...

                              • 12. Re: Chart with data and dates
                                SteveFinch

                                     Oh man!!!
                                     Can I say I love you!