calculation that chooses the sort field
I'm using FMP 8 for a MAC.
I have the following fields - Total number of Hours, Total Budget.
I also have three date fields - Greenlight Date, Production Start Date, and Air Date
I want to run three different reports using the date fields.
I then want my Total number of Hours and Total Budget to appear in a particular Quarter column (i.e. 1Q, 2Q, 3Q, 4Q) depending on the sort.
So essentially - a greenlight date sort could show my hours and budget date appearing in 1Q (Jan - March of the sort year), but the same yearly report by Air Date would have the data appear in the 4Q column.
How do I do this?
I'm assuming I create a Quarterly field that has a calculation whereby it imports the numbers and hours automatically based on the sort field (in this case the date).
So a 1Q Hourly Field might be:
When Sorting by __________ Field, then if dates of __________ field are between Jan and March of SORT YEAR; Total Number of Hours; 0