Deriving a date based upon past # of months

[bcanfiel83]

Hi All,

I have a somewhat unusual requirement (at least to me) that I'm trying to figure out how to accomplish. In the query that I'm running, there's a column which displays a number representing the number of months, i.e.: 24, 36, 48, etc. 

What I'm attempting to do is take that number and create a new field which takes today's date and then subtracts the number of months to derive a prior date.

For example, if the # of months is 36, then the field would display "08/29/2021" ; essentially the same thing that this is doing: 

https://www.timeanddate.com/date/dateadded.html?m1=8&d1=29&y1=2024&type=sub&ay=&am=36&aw=&ad=&rec=

I'm not exactly sure where to begin with this one, so any help getting started would be greatly appreciated.

Thank you!

[ITWhisperer · Accepted solution]

| eval previous_time=relative_time(now(),"-".months."mon")

You would have to be careful around leap years and if the months is not a multiple of 12.

If you know the months is always going to be a multiple of 12, you could do this instead

| eval previous_time=relative_time(now(),"-".floor(months/12)."y")

[bcanfiel83]

This is great, thank you!!