I have the following SQL calculation:
sum ( case when Case_Type='General Liability' then 1 else 0 end ) GLCount,
sum ( case when Case_Type='Auto' then 1 else 0 end ) AutoCount,
sum ( case when Case_Type='Workers Compensation' then 1 else 0 end ) WCCount,
sum ( case when Case_Type='Other' then 1 else 0 end ) OtherCount
GROUP BY Intake_Month_Name"
; "," ; ¶
Here's what the data viewer looks like with the result: http://cloud.zerobluetech.com/image/3Y1u2S33460L
The charting_case TO is connected to charting, and the records are constrained to the entire year before the last of this month. This works fine and FileMaker returns the correct data set when checked.
I want to see a comparison from month-to-month. So I need a number for every month for every case type. In the case table I have a calc that is the Intake_Month_Name (returns the month of the date).
Now, when I constrain the records to just show the General Liability for last September, FM gives me 33 records and SQL gives me 385.
So ideally I'd like to have a dataset that looks like this:
where the rows will be the months and the letter would be substituted with the proper count of case types for each. I tried grouping by the Intake_Month_Year field, as well, but then I get even more wacky results.
Can anyone can help me tackle this?