Taking Date Stamp and Subtracting Months

[#1]

Hi All,

I tried searching the forums and couldn't find a solution to this problem. I have a date stamp lets say its 2012/04/01 and a variable that represents the number of months the user wants to go back, lets say the value is 10.

How do i create a new field called MinDateRange where the value would be 2011/07/01.

I managed to find a way to do this but it is quite lengthy and im sure there is an easy way. For now this is what my code looks like... Max months a user can go back is 12 hence why this type of hard coding was possible

MakeDate(

if(Month(MaxDateRange)>$(zAccount$(zAccountNumber2)AveragePeriod),

          Year(MaxDateRange),

          Year(MaxDateRange)-1),

if(Month(MaxDateRange)>$(zAccount$(zAccountNumber2)AveragePeriod),

          Month(MaxDateRange)-$(zAccount$(zAccountNumber2)AveragePeriod),

          12-fabs(Month(MaxDateRange)-$(zAccount$(zAccountNumber2)AveragePeriod))),1)

as MinDateRange

As you can see very messy

Thanks in advance all

Cheers,

Byron

[jonathandienst · Accepted solution]

Hi

You missed the AddMonths (and AddYears) function! I am not sure what you are trying to do with the ....AveragePeriod variable, so perhaps I am missing the point. So you could do something like:

     LOAD

          MaxDate,

          AddMonths(MaxDate, -$(vReportPeriod)) As MinDate

          ....

     FROM ....

Hope that helps

Jonathan

[christophebrault]

Hi,

I think you can use the MonthStart() function. It Takes Two parameters :

(Your Date Stamp, Number of period)

Then in your situation :

MonthStart(MaxDateRange,-10) as MinDateRange,

Put a variable instead of -10 and it's done.

Hope this will work in your case

[#4]

Hi all,

Thanks for the quick response. I'm busy trying both now and will let you know.

Jonathan - The unnecessarily long variable is part of a loop that will go through each account and apply the necessary average. The user selects the account and the number of periods and then reloads the data. I am essentially trying to create a resident table that has forecasted based on the inputs from the user what the projected values would look like for the next n number of years. Crossing fingers it just works

[#5]

Ended up using the Addmonths function, worked like a charm

Thank you both for your reply

Cheers,

Byron

[christophebrault]

Yes, i made a mistake uing monthstart() because it works for month but gives the first day in the same time and that was not exactly your need.

I'm interessted on your project, share your progress.

Bye

[#7]

Yeah, the addmonths worked like a charm.

No problem Christopher. I will upload my test document where i was testing the script on a piece of data and what the result of that was. Would be great to get a second opinion. Btw, I'm an analyst trying to program so my code is not always nice to look at If you have any pointers that would be fantastic

Have a great day

Byron