Using start and end dates for year on year analysis

[Andy H]

Hi there,

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:

01/11/09 - 04/11/09 $400

05/11/09 - 22/11/09 $450

23/11/09 - 01/12/09 $400

The above is an example of some rates for 2009 and below is an example of the rates from 2010 that I'd like to compare:

01/11/10 - 03/11/10 $440

04/11/10 - 20/11/10 $450

21/11/10 - 29/11/10 $440

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....

Many thanks for your advice

[comment]

Well, it doesn't seem too difficult, except for this: how do you want to handle the case where one of the two years being compared is a leap year?

[Andy H]

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.

[comment]

I suppose that too would be possible, but rather difficult to build - because Filemaker automatically adjusts Date ( 2 ; 29 ; ) 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.

YearsDailyComparison.fp7.zip