How to perform calculation on a field with a date 3 years ago, but only use the field for the latest date of that month?

[peterkn]

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.

[sundareshr · Accepted solution]

One way you can do this is by adding a new field with the latest date for REPORT_DATE by month/year. You can do that like this

... | rex field=REPORT_DATE "(?<dt>\d{1,2})\/(?<mmyyyy>.*)" | eval myyyy=trim(mmyyyy, "0") | streamstats max(dt) as latest by  myyyy

Hope this gets you what you're looking for.

[peterkn]

It does help me get to compare the dates, especially with the

max(dt) as latest

. Many thanks.