I've been scouring the forums and racking my brain to try and find a solution for the following problem:
I'm looking to be able to compare rates on a year on year basis down to the day and or week. To enter the data in a user friendly format I have 3 fields; a start date, end date and rate that is applicable for the period. For example:
As you can see the rate period may vary by a few days each year hence why I'd like to be able to drill down to a day by day analysis as well as week by week compared to the previous year.
I am able to create a field that generates all the days between the start and end date, but I'm struggling to be able to implement this in any usable format so that I can calculate year on year rate differences. The kind of result I'm looking to achieve is a percentage increase YOY:
Day 2009 v. 2010
01/11 +10%
02/11 +10%
03/11 +10%
04/11 +11%
05/11 0%
06/11 0%
etc....
That's a good point. In the event of a leap year e.g. 29 Feb 2012 the rate cannot be compared to the previous year or subsequent year since the 29th Feb will not exist. In this instance I'd treat the 29th Feb as +100% increase in rate from the previous year.
I suppose that too would be possible, but rather difficult to build - because Filemaker automatically adjusts Date ( 2 ; 29 ; <non-leap year> ) to March 1.
The attached file is very easy to implement and it simply skips February 29. It shouldn't be too hard to adjust it to include February 29 when both years being compared are leap years.
Tweet This Post!
Welcome Andy H's first post!
Attachment: 
