4 Replies Latest reply on Apr 9, 2014 11:42 AM by VelvetDoofus

    Graphing based on unique values - I'm not smart

    VelvetDoofus

      Title

      Graphing based on unique values - I'm not smart

      Post

           Hello,

           I am fairly new to filemaker and had a quick question. I am trying to create a pie chart based the following example:

      Name,Temperature,Goob,FavoriteShape, Note, Unique Squares, Unique Circles

           1- Joe,Cold,JoeCold,Square,Blah,1, 2

           2- Joe,Cold,JoeCold,Square,Blooh,1,2

           3- Fred,Cold,FredCold,Circle,Blah,1,2

           4- Joe,Cold,JoeCold,Circle,Blah,1,2

           My issue is, I only want to graph by Favorite Shape based on Distinct Name and Temperature (using the goob field). Therefore in the example above, there would be only 3 unique records, 1 (or 2 -just not both),3 and 4. The results I'm tryin to get to show up in a graph would be a pie chart with Square having a value of 1 and Circle with a value of 2, based on the unique records. Anytime I try and graph, I get four records, 2 squares and 2 circles, because I cannot get it to only graph the uniques. Is there a solution to this problem?

           To get the values, on a larger data set, I created the two calculation fields listed below: 

           ExecuteSQL ("SELECT COUNT (DISTINCT Goob) FROM Table WHERE FavoriteShape = 'Square' "; "" ; "")

           ExecuteSQL ("SELECT COUNT (DISTINCT Goob) FROM Table WHERE FavoriteShape = 'Circle' "; "" ; "")

           I  combined the name and temperate fields (=name + temperature) into the goob field to assist with the issue. 

           Some of you are seeing this and thinking, man this guy is a moron. I have no defense other than I'm new. Any help would be awesome.

           If I could create another field to display the shape only if it was unique, I believe i could graph then.

      Name,Temperature,Goob,FavoriteShape, Note, Unique Squares, Unique Circles, New Field

           1- Joe,Cold,JoeCold,Square,Blah,1, 2, Square

           2- Joe,Cold,JoeCold,Square,Blooh,1,2,       <---would be blank 

           3- Fred,Cold,FredCold,Circle,Blah,1,2, Circle

           4- Joe,Cold,JoeCold,Circle,Blah,1,2, Circle

           Thoughts?