Graphing based on unique values - I'm not smart

Question asked by VelvetDoofus on Feb 26, 2014
Latest reply on Apr 9, 2014 by 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?