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

# Graphing based on unique values - I'm not smart

### 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?

• ###### 1. Re: Graphing based on unique values - I'm not smart

You're definitely NOT a doofus.

I still think that there's a way to use ExecuteSQL to produce the data you want so that you can do a chart from delimited data, but I've been playing with different queries against your sample data off and on for over a day and still haven't come up with one that works.

An alternative approach would be to set up a table where the duplicate do not exist and thus do not need to be omitted.

And I suspect that the "sum the reciprocal" method could be adapted to the needs of this chart: How to count the number of unique occurences in field.

But an SQL query still "feels" like the better option if someone can just come up with the right query that will work...

• ###### 2. Re: Graphing based on unique values - I'm not smart

PhilModJunk,

Thanks for the info. I actually tried something thatI thought would work, essentially marking duplicates then working off that result. Ill let you know if it works :)

• ###### 3. Re: Graphing based on unique values - I'm not smart

Yes, if you perform a find to omit the marked records, that should work.

• ###### 4. Re: Graphing based on unique values - I'm not smart

PhilModJunk,

It did work - using the Mark method! Thanks!

VD