5 Replies Latest reply on Jun 13, 2015 11:26 PM by mrb13676

    Chart record counts in found sets

    mrb13676

      Title

      Chart record counts in found sets

      Post

      HI

      Have had a look around the interwebs and havent been able to find a coherent answer to this. 

      I wish to create a chart. The data I want to chart is the ages of people in the database. I have a field which calculates age on the date of record creation and I want to chart this field, broken down into age groups i.e <1y, 1-5y, 5-10y and >10y.

      Can anyone help with how to do this? I can do a find using the age criteria and then I get a number of records but I can't get these to show on a chart. Do I need to make the chart using a different table?

      Thanks

      Mike

        • 1. Re: Chart record counts in found sets
          philmodjunk

          You don't need to chart found sets, just group your records by age group and then use a summary field that "counts" a never empty field as the Y-series value for your chart.

          If you don't already have an age category field, you can define a calculation field that does, such as:

          Case ( ageField < 1 ; 1 ;
                     ageFied < 6 ; 2 ;
                     ageField < 11 ; 3 ;
                     4 )

          You'll need to sort your records on this field and make sure that your data source options for your chart specify "found set" and "summarized data when sorted". (Exact wording/details for these settings vary with the specific version of FileMaker in use.)

          • 2. Re: Chart record counts in found sets
            mrb13676

            Thanks PhilModJunk

            I'm still struggling though.

            I've defined an age category field which (cAgeCategory) looks like this 

            Case ( CAgeProcDate < "1" ; 1 ;
                       CAgeProcDate  ≤  "5" ; 2 ;
                       CAgeProcDate  ≤ "10" ; 3 ;
                       CAgeProcDate  ≥ "70" ; 4; 
                       5 )

            I've also defined a summary field (sAge) which is a count of cAgeCategory.

            However, when I use cAgeCategory on x axis and the summary on the y-axis I only get a horde of numbers along the x axis, no chart at all. this may be a bit unclear but trying to explain as best I can.

            Thanks

            Mike

            ]

            • 3. Re: Chart record counts in found sets
              philmodjunk

              Get rid of the quotes in your calculation. These are numbers not text.

              You need to use a different a different field, eventually, for the x axis as I assume you won't want the values 1, 2 ,3 and 4 to appear there. You can use the same basic calculation in a different field, but specify a more informative value than a simple number.

              But the biggest problem is that you forgot to sort your records by cAgeCategory. This is necessary in order to group your records by category and get one point in the Y-series data for each group of records.

              • 4. Re: Chart record counts in found sets
                mrb13676

                Aha.

                It seems to be working more or less now.

                This is the new calculation 

                Case ( CAgeProcDate > 70 ; ">70" ;
                           CAgeProcDate > 18 ; "Adult" ;
                           CAgeProcDate > 10 ; "Teen";
                           CAgeProcDate > 5 ; "5-10" ;
                           CAgeProcDate  ≥  1 ; "1-5" ; 
                           CAgeProcDate < 1 ; "Infant";
                "meh" )

                The only problem I'm having now is that on my chart i have only 4 columns instead of 6, I don't see the "Teen" or "Infant" categories,  all of them are included in the "Adult" summary. Weird. (I have the layout on which the chart appears set to run a script sorting on cAgeCategory on entry).

                Any further ideas?

                Mike

                 

                 

                • 5. Re: Chart record counts in found sets
                  mrb13676

                  OK, sorted it out.

                  I hadn't read PhilModJunk's second post properly. The Case function doesn't seem to enjoy assigning text values to a numeric field (who would have thought that? smiley)

                  SO I left the age category field getting numbers assigned to it

                  Then in the chart setup I used a calculation to assign text values to the x axis base don the values of the age category.

                  Now working like a charm.

                  Thanks so much!