A summary field that computes the total of your field will compute a year to date total if only records for the current year are present in the found set. If you pull up records from more than one year, you can still show yearly totals if you use this calculaiton field:
Year ( YourDateField )
and set up a sub summary layout part "when sorted by cYear" and then put the summary field in this part and sort your records by cYear.
A relationship can also be used to compute totals for this year and also for the previous year.
Thank you very much PhilModJunk;
This is actually a good idea even thought it is really not how I wanted it but I have never thought of that way.
It's not the only option. In fact, I briefly mentioned a second approach.
How else would you apprach this matter?
It depends on what you need.
This relationship might serve, but it depends on your database design whether it will work as I post it or need further modification:
Sales::cYear = SalesSameYear::cYear
SalesSameYear is a second occurrence of Sales that you create on the Relationships tab in Manage | database by selecting it and then clicking the duplicate button (two green plus signs).
Then, on a layout based on Sales, you can add SalesSameYear::sTotalSales
to show the yearl to date total. (sTotalSales is the summary field I described in my first post.)
A calculation field defined as Sum (SalesSameYear::SalesAmountField) will compute the same total.
You can create yet another occurrence of your table and relate it as:
Sales::cLastYear = SalesLastYear::cYear
to get the same totals for the previous year. cLastYear would be defined as: Year ( SalesDate ) - 1.
Note that these relationships match to all records in the table with the specified value in cYear. If you need to match more selectively we'd have to change the relationships to include one or more pairs of fields to make the match more selective.
In fileMaker 11, one row filtered portals can also be set up with the sTotalSales field to show different yearly totals and you then do not need to define as many self join relationships--but it's an approach that doesn't work well in every situation.
I just wanted to share my sollution with you on this issue (YTD sales Report). What i have figured was that I calculated date as a number by creating a new field and on my search i put "1...Current date" and that gave my the numbers for this year and also for last year for the same range. Then i created a layout with subsummary stuff. It came out beautifully:)