6 Replies Latest reply on Mar 24, 2015 9:07 AM by JimMiller_2

    Chart how-to: Count of factor 1 vs. factor 2?

    JimMiller_2

      Title

      Chart how-to: Count of factor 1 vs. factor 2?

      Post

      I'm new to Filemaker, and am still trying to figure out how its charting system works.  I've got a database that contains, among other things, columns for "age" and "household income".  Each row has a value for these columns, like so:

             
      • Age: One of "18-24", "25-34", "35-50", and so on.
      •      
      • Household income: One of "less than $25,000", "$25,000 - $35,000", and so on.

      I'm trying to get a graph containing sets of columns that show the number of people in each cell of the table, if you will:

             
      • For age = 18-24, the numbers of people in each of the income groups
      •      
      • For age = 25-34, the numbers of people in each of the income groups,
      •      
      • and so on

      Pretty straightforward, but I just can't figure it out.  I'm guessing it has something to do with creating leading or trailing groups, but, again, I'm just not there.  Can anybody offer some advice?  Thanks!

        • 1. Re: Chart how-to: Count of factor 1 vs. factor 2?
          philmodjunk

          What kind of chart? a line Chart? stacked bar chart?

          but then you refer to "leading or trailing groups" which suggests that your "chart" might be either a summary report or a cross tab "chart" of this data...

          • 2. Re: Chart how-to: Count of factor 1 vs. factor 2?
            JimMiller_2

            A stacked bar is probably the right thing, but right now I'm just trying to get both aspects of the data into the chart.

            My table looks something like this:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
            idageincome
            118-24Less than $25,000
            225-29$25,000-$40,000
            325-29$25,000-$40,000
            430-40$40,000-60,000
            518-24Less than $25,000
            625-29Less than $25,000
            718-24$25,000-$40,000
            818-24$25,000-$40,000
            930-40$100,000-$150,000

            and I'd like to get a stacked-bar chart that shows:

            * There are 4 people 18-24; 2 have income "less than $25,000" and two "$25,000-$40,000"

            * There are 3 people 25-29; 1 less than $25,000 and 2 $25,000-$40,000

            and so on.  Does that help?  Filemaker *has* to be able to make a chart like this (right?); I just can't figure out how...  Thanks!  (btw: Safari is not playing well with this text widget; I hope this comes out in an intelligible form...

            • 3. Re: Chart how-to: Count of factor 1 vs. factor 2?
              philmodjunk

              Your example data shows the categories, but how do you record the counts? one record for each person--possibly in a related table or one record for each category with a field that shows the count?

              Browser Hint, I use Safari with my iPhone at times to post here and I've discovered that things work better if I position the cursor to the left of an extra character. Then pressing return no longer pops back to the top of this text area box.

              • 4. Re: Chart how-to: Count of factor 1 vs. factor 2?
                JimMiller_2

                The table is meant to show one record for each person; in this table, there would be nine people -- that's the raw data that's come out of the survey software that produced it.  Within Filemaker, I've experimented with computing those summary counts via leading or trailing groups; I can do that, but I can't find any way to get those values into a chart.  (They seem to be in the layout view but not really in the table?)  I guess I could maybe create another table that pulls counts of records out of this table and into it, but is that really necessary?

                Meanwhile:

                * Thanks for the help!

                * The cursor bug we're seeing is due to a known incompatibility between CKeditor (the third party that makes this widget) and Safari --  Filemaker is. unfortunately, behind on its updates.  The bug does not happen in Chrome or Firefox (including the iPhone version of Chrome, btw...)

                • 5. Re: Chart how-to: Count of factor 1 vs. factor 2?
                  philmodjunk

                  I would suggest that Oracle RightNow is behind in its updates as they produce this so called software (which has long had many issues and poor support from Oracle RightNow.)

                  in order to use a stacked bar chart, you need a separate Y series for each portion of the bar--just like you would need a different y-series for each line of a multiple line line chart.

                  So in your case, you need one Y-Series for each income category with a bar for each age category. This will require two fields for each Y-series. Define a calculation field like this: income = "Less than $25,000" with a number result type. Then define sL25K as a summary field that sums or counts this calculation field. Do the same for each income category so that you have a different pair of calculation and summary fields for each age category. Then you can specify the summary fields as your Y series values and make sure that your data source settings specifies the "summarized data" option.

                  Final step is to find the records you want for you chart and sort them by Age category. (If you don't get the correct left to right order of your columns, you may need to add a calculation field that converts the age categories into number values that, when you sort on this added calculation field, produce the needed order.)

                  • 6. Re: Chart how-to: Count of factor 1 vs. factor 2?
                    JimMiller_2

                    I think I'm getting it -- thanks very much!  I'll dig into this once I clear some other stuff, and report back.