4 Replies Latest reply on Jun 2, 2016 2:53 PM by louisindc

    Simple charting question, perhaps....

    louisindc

      Newbie to FileMaker Pro 15 (Mac) and I can't seem to figure this out after loads of trying and searching.  Hopefully I can explain this.

       

      I have two fields which we'll call A & B.

       

      Both fields have data in them that's more or less like this:

       

      Field A: 1, 2, 3, 4, 5, 6, 7, 8

      Field B: apples, oranges, apples, apples, oranges, apples, apples, oranges

       

      Field A is related to B in that if Field A #2 is related to oranges.

       

      I can easily make a chart that will plot B against A (A on x-axis).

       

      But what I want to do is make a chart that plots just the oranges in B against A.

       

      Does this make sense?  In other words, how do I just select specific data points in field B to consider and not the entire data set in field B?

       

      Many thanks, and apologies if this is way to easy....

      louisindc

        • 1. Re: Simple charting question, perhaps....
          siplus

          If I understood what you want, you might try the following:

           

          - define a global field (gFruitName) - you can put it on the layout as pop-up

           

          - create a calculation field - "PlotThis", defined as If ( B = gFruitName; 1; 0 )

           

          - plot PlotThis against A

          • 2. Re: Simple charting question, perhaps....
            louisindc

            Thanks siplus.  I tried it and that doesn't seem to do it. 

             

            I think this would work if I only had two categories in B, but I actually have 10 (I just wanted to keep the example simple) amongst 300 records.  There's actually 14 categories in A and 10 in B.  I want to be able to plot any one (or two or three) of the 10 in B against all in A.  Just not all of B at the same time.

             

            It seems there should be a way to just select the data I want in B to be plotted rather than plotting the whole thing.

             

            Thanks again.

            • 3. Re: Simple charting question, perhaps....
              erolst

              louisindc wrote:

               

              I just wanted to keep the example simple

               

              That's not always a good idea, not the least because it actually adds a level of complexity: now we have to try and keep a mental model of what you might mean, or what apples and oranges have to do with numbers…

               

              Anyway, you can try this: assuming you have a (return-delimited, like in a checkbox-formatted field) list of categories to filter B by …

               

              Let (

              arrayOfBs = "'" & Substitute ( YourTable::selectionFieldForB ; ¶ ; "','" ) & "'" ; // assuming that B is a text field

              ExecuteSQL ( "

                 SELECT SUM ( A )

                 FROM YourTable

                 WHERE B IN (" & arrayOfBs & ")

               

                 GROUP BY B

                 " ; "" ; ""

                )

              )

               

              Using the “return delimited values” option of the Chart tool, this result would be your y values, while the original selection field are your x labels.

               

              Using SUM() is of course just an example, since it is not clear (to me) how you want to aggregate the A field in the found records.

              • 4. Re: Simple charting question, perhaps....
                louisindc

                Thanks erolst and sorry for the complexity issue.  I’m not really good at this kind of thing….  I’ll give this a shot.