12 Replies Latest reply on Mar 10, 2011 4:22 PM by philmodjunk

    Where is chart data?

    brett_

      Title

      Where is chart data?

      Post

      I've created a new database with 40 rows and 15 columns.  I switch to Layout and drag a chart into the footer area.  I set its Y and X axis each to a different column.  It is set to use current found set.  The X axis includes five days of week.  The Y axis should be a count of colors associated to each day of week (there are six colors).

      I save Layout #2 and switch back to Browse and select Layout #2. The chart is there but no data is displayed.  The Y axis goes up to 200.  The X axis seems to cram in every instance of five days...it looks almost like a barcode.  Completely unreadable.

      There are only five possible days to choose from on this particular column.  In that way, it is like an index.  Why didn't the chart print five values on the x-axis rather than what appears to be 40 values on the x-axis?

      I did try using a calculation on Color in the chart via Sum().  But there was no difference.

        • 1. Re: Where is chart data?
          philmodjunk

          What happens if you put the chart in the body? (The layout part where you place the chart can make a difference. See FileMaker Help for more on this.)

          What options did you select for your chart?

          Note, FileMaker doesn't actually have "rows" and "columns" it has Records  instead of rows and Fields instead of columns. That's just a change in terminology, but correct terms can help avoid confusion when speaking "FileMaker". Wink

          • 2. Re: Where is chart data?
            brett_

            Thanks.  I did put it into the Body area but get the same result.  Any other suggestions?

            • 3. Re: Where is chart data?
              brett_

              Ok, it seems to be a case of figuring out how to get the right things on the axis.  I changed the y-axis from color (text) to a price (number).  Now I'm getting bars on the y-axis but it is still trying to plot every single day on the x-axis, which of course results in the barcode on the x-axis.  I've changed it so it is summing prices, which I'd think means sum by day.  Same results.  Days print for every instance of day (40 rows).

              • 4. Re: Where is chart data?
                philmodjunk

                Yes you can only chart numbers on the y axis.

                So each record (row) represents one day? What should each bar on the chart represent, one week? one month? ....

                If you define a summary field to compute totals of the values you want for your Y axis, you can use the "Show data points for groups of records when sorted" to create chart bars for each group of records. You'd need to first sort your data on a field that successfully groups your records to match what you want for your chart bars.

                • 5. Re: Where is chart data?
                  brett_

                  So each record (row) represents one day? What should each bar on the chart represent, one week? one month? ....

                  Yes - each row is one day.   Each day has an associated color (text), of which there are six colors to choose from.  There should be five values on the x-axis:

                  - Monday
                  - Tuesday
                  - Wednesday
                  - Thursday
                  - Friday

                  The y-axis should consist of color count for each day.  I'm guessing I can only do one color per chart.  Red days for example.  But I still cannot get the chart to do this.

                  I have these settings:

                  x-axis is "table1::Day of Week"
                  y-axis is "table1::Color = "Red" 

                  y-axis is a calculation field.

                  • 6. Re: Where is chart data?
                    philmodjunk

                    If each record is one day. How do you get more than one "color" to count for a given day?

                    • 7. Re: Where is chart data?
                      brett_

                      If each record is one day. How do you get more than one "color" to count for a given day?

                      For example:

                      5/6/2001 (Tuesday) = RED
                      5/6/2003 (Tuesday) = RED
                      8/16/2004 (Tuesday) = RED
                      5/6/2007 (Tuesday) = RED
                      5/6/2008 (Tuesday) = RED 

                      Those didn't occur on Tuesday but just as example, say they did.  Now in the chart, Tuesday is on the x-axis and will have a count of 5, since the chart is based on the color red.  The other four days may have counts of 3, 15, 8, 9.  So I should see those bars on the y-axis.

                      • 8. Re: Where is chart data?
                        philmodjunk

                        OK,

                        First you need a group field so that when you sort records all records for the same day of the week fall are grouped together

                        a calculation like cDayOfWeek, defined as DayOfWeek ( DateField ) will serve for that. It will return 1 for Sunday, 2 for Monday ....

                        Now define a summary field as the "count of" any never blank field in your table. The date field we are using here will serve just fine.

                        Now in your chart, specify "Show data points for groups of records when sorted" and use this summary field for the Y axis. Make sure you sort any record set you select for this chart by including the cdayOfWeek field in the sort order.

                        You can probably sort your records first by day of the week and then by color to get a chart that graphs all colors with a separate bar for each day and for each color, grouped by day of the week.

                        • 9. Re: Where is chart data?
                          brett_

                          Thanks.  Can you explain the first part again?  I don't understand how it groups using DayOfWeek.  What is the difference between cDayofWeek() and DayofWeek()?

                          • 10. Re: Where is chart data?
                            philmodjunk

                            cDayofWeek is the name I gave to the calculation field. I use a lower case c as a naming convention to help identify fields of type calculation.

                            DayOfWeek() is the name of the FileMaker function that it uses.

                            • 11. Re: Where is chart data?
                              brett_

                              Ok.  For the x-axis value, do I specify a calculation and use:

                              DayOfWeek(Date) = "Tuesday"

                              I tried that before but still got the same messed up chart.  I'll work on the count of field.

                              • 12. Re: Where is chart data?
                                philmodjunk

                                DayofWeek returns a number from 1 through 7 not a name. This calculation field is used for sorting the data so that the grouped data chart option will work. You'll need the summary field before you can get any data to chart.

                                There's a different function that returns the day name.