Stu412 Posted September 7, 2015 Posted September 7, 2015 I'm looking to see if there's a better way to do what I'm currently doing, which is effectively making cross tab tables from a single column of data. My main data table columns are (in a very simplistic format for digestion here): Date Amount TrueFalse1 TrueFalse2 TrueFalse3 There are around 70,000 records, each with entries in every field above. Records tend to be grouped into months and I need to get columns per month on a report. To do this, I use CASE statements initially and then summarise the results: JanTotal::Case ( TrueFalse1 = "True" and TrueFalse2 = "False" and Date = "Jan" ; Amount) FebTotal::Case ( TrueFalse1 = "True" and TrueFalse2 = "False" and Date = "Feb" ; Amount) MarTotal::Case ( TrueFalse1 = "True" and TrueFalse2 = "False" and Date = "Mar" ; Amount) sJanTotal sFebTotal sMarTotal I now have 6 fields just for three months with this logic testing. I also need to have other variations on TrueFalse1/2/3 per month as well, so the numbers of fields can easily bloat. In the humble opinion of those with more FM knowledge and experience of me, is this an efficient way to do things? Is it more efficient to use a 'temp' table and have this reload data each time I need it to? Any input appreciated, thanks in advance
OlgerDiekstra Posted September 7, 2015 Posted September 7, 2015 (edited) Filemaker can't easily do a column based summary. It can do a row based summary though. For that, you need to create a month field calc that calculates the month from the date (ie month ( Date). Then create a summary field total that totals Amount. Next create a list layout with a subsummary part (sort it by month), that holds the month and the total. Now sort by month. You can forgo the case statements. Edited September 7, 2015 by OlgerDiekstra add detail
Recommended Posts
This topic is 3365 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now