I'll mark this one in Favorites to play with the SQL later today if I get a chance, but did you know that you can get your stacked bar chart without using Execute SQL?
With the right combination of calculation and summary fields, you can get a stacked bar chart with a Male and Female portion of the bar and with the bars representing groups of records.
Even though I know I could get a stacked bar chart without ExecuteSQL, I decided to go for the SQL solution as I have multiple tables and a dashboard. I'm quite new to FileMaker and to be honest, the relationship graph has gotten the best of me. I find it a lot easier to work with the ExecuteSQL function, rather than having to create multiple table occurrences and relationships.
However, for this particular chart I am only displaying data from the table that the layout is based on, so I wouldn't mind a quick explanation on how to do it the old-fashioned FM way. :)
Ok now I discovered that the statement above actually does work if I specify "Female", but not if I specify "Male". So I don't know what to think, could it be a bug? I just can't see what could possibly be the difference. Of course, then I don't know whether it's just a coincidence that the female co-workers end up in the right columns and the males don't...
Maybe it's a matter of case sensitivity. Text comparsions and sorts in SQL expressions are case sensitive where they are not in FileMaker. Could some of your data store "Male" and others "male" or "MALE"?
The "old school" method for getting your stacked bar chart works like this:
Define two calculation fields that only contain data for one of the two genders:
cMale: If ( Gender = "Male" ; 1 )
cFemale: If ( Gender = "Female" ; 1 )
Define two summary fields, sMaleCount and sFemaleCount, that compute the count or total fo cMale and other for cFemale.
Then use sMaleCount and sFemaleCount as your two Yseries values for your stacked bar. The data source setting for your bar chart has to be for the found count, summary values option for this to work and then you need to perform a find and sort your records by Group to get your chart. And note that by changing the sort order or by performing finds to get different found sets, you can get different results in your chart.
Is your list of member groups finite?
I've checked for case sensitivity and made a value list for the gender field just to be certain so that's not it. The group list is finite yes, there's only four groups.
I've tried to do this with other data as well, I even tried specify a join so as to filter by two criteria, but whenever I add the second criteria, the data "shifts" in the chart and is displayed in the wrong columns.
Perhaps this might help...
XAxis = "Female¶Male"
YAxis1 = ExecuteSQL( "SELECT Count(*) FROM Table WHERE Upper(Group)='GROUP NAME1' AND Active=1 GROUP BY Gender"; ""; "")
For each Group add another Y axis.
YAxisn = ExecuteSQL( "SELECT Count(*) FROM Table WHERE Upper(Group)='GROUP NAMEn' AND Active=1 GROUP BY Gender"; ""; "")
And yes, the query is case sensitive.
Now... If you encounter zero/missing values, then you might have a problem with data in the wrong columns. Here is a custom function I wrote that helps with that. It is recursive. Just send the XAxis as a list and send the YAxis data delimited with the pipe character.
You will then adjust your YAxis query to this.
YAxis1 = ExecuteSQL( "SELECT Gender, Count(*) FROM Table WHERE Upper(Group)='GROUP NAME1' AND Active=1 GROUP BY Gender ORDER BY Gender"; "|"; "")
Now do this for each YAxis.
Distribute( xAxis; YAxis; 1; 1; “”)
I hope this helps.
Distribute( Key; Data; K; D; Result) NOTE: K and D must start with 1 and Result must be ""
//Used to distribute data for graphing that may have no data for some values.
//Both the Key and Data should be ordered the same.
//Key: The list of values representing the X axis.
//Data: The list of value pairs (Key|Value) representing data on the Y axis. !!Use the pipe character for delimiting!!
//Syntax: Distribute( Key, Data, 1,1,"")
N=ValueCount ( Key );
M=ValueCount ( Data );
X=GetValue( Key; K);
Y1=GetValue( Substitute( GetValue( Data; D); "|"; "¶"); 1);
Y2=GetValue(Substitute ( GetValue( Data; D); "|"; "¶"); 2);
Result = If( GetValue( Result & If( X = Y1; "¶"&Y2; "¶"&0); 1) = ""; Right( Result & If( X = Y1; "¶"&Y2; "¶"&0); Length(Result & If( X = Y1; "¶"&Y2; "¶"&0))-1); Result & If( X = Y1; "¶"&Y2; "¶"&0));
Match = (X=Y1)
(Key="") or (Data="") or (K<1) or (D<1); "No Data";
Match; Distribute( Key; Data; K+1; D+1; Result);
Distribute( Key; Data; K+1; D; Result))
Male | Female suggests to me that you may have used a check box set or radio buttons for specifying gender. Please note the following three possible complications so that we can rule them out as possible factors:
Such a value list format can hide data present in the field that does not match any of the current values in your value list. It's unlikely, but you could have "Apple" as the sole value in this field and it would appear blank (no values selected) when formatted with checkboxes/radio buttons using a Male | Female list of values.
The captitalization of the values in the value list will conceal the actual capitalization of your data in the field. You could have "MALE" entered in the field but it could appear as "Male" due to the value being entered that way in the custom value list.
The third issue explains a possible way the the first two might occur: If you modify the values in your value list, those changes have no effect on the actual data previously entered into the field. Thus, you might have had a value of "male" in the value list and used it to enter data for a while and then you corrected the value in the value list to be "Male". This then looks correct on your layotu, but the actual text is still "male" in records where you selected that value before editing the value list.
If there is any possibility that this is a factor, you can make a copy of your field and return the copy to Edit Box format in order to inspect the actual data in your field.
Thanks a lot for your feedback. None of the possible complications listed by PhilModJunk is the problem though. And I don't get your suggestions to work at all Daniel, although I must admit the last one is out of my comfort zone. But don't I need the Group data on the y-axis?
Everything is correct except the data is shifted/displayed in the wrong columns, it's like the axes can't "communicate".
No worries. If you still need help and want to send some screenshots, I'll see if I can help. Are you familiar with Custom Functions?