I need to do calculations on fields where the Date is 3 years ago. However, there is a twist. I need to find this field where it was 3 years ago, and then minus the same field, but 3 years and 1 month ago.
Let's assume we have 2 fields with the following data
REPORT_DATE POPULATION
11/12/2012 200
29/12/2012 195
2/01/2013 190
30/1/2013 215
5/02/2013 222
11/02/2013 230
28/02/2013 235
26/03/2013 240
I would like to calculate the numbers dropping off which is the population of today's date, but 3 years ago minus today's date day, but 3 years and 1 month ago.
Let's say today's date is the 6th of January 2016. The number dropping off for January 2016 is the Population that was 3 years ago (30/1/2013) takes the population of 3 years one month ago (29/12/2012), so 215 - 195 = 20. Please notice that for each month there could be 1,2 or even 3 REPORT_DATE values, we just want to take the latest date of that month (say for December 2012, we get the 29/12/2012 instead of the 11/12/2012). We do the same calculation for February and March. So the result would be
Jan Feb Mar
-5 7 5
I have managed to get the current date 3 years ago, but I'm struggling to get the value of the latest day of that date 3 years ago (29 instead of 11 for example) and perform the calculation.
Any help is greatly appreciated.
... View more