Calculate next Cycle Date

[gerhardl]

Hi there,

I currently have the following expression in my Load:

if(day(DATE(DATE#( [Posted Date], 'DD-MMM-YY')))<11,month(DATE(DATE#( [Posted Date], 'DD-MMM-YY'))),month(date#(month(date(DATE#([Posted Date] ,'DD-MMM-YY')))+1,'M'))) as [Posted Cycle],

What this does is look at a transaction date (Posted Date), and then determines what the Statement Cycle Month will be (Posted Cycle) for teh transaction.

If the Transaction happened before the 11th of the month, the Cycle Month will be the same month (e.g.7 November, Cycle Month is November).

If the Transaction happened after the 10th of the month, the Cycle Month will be the nextmonth (e.g.11 November, Cycle Month is December).

We cycle on teh 10th of every month - I want to change the expression to give me the cycle DATE, not just cycle month.

So in the above example it must give me 2012/11/10 (1st example), or 2012/12/10 (2nd example).

Thanks in advance,

Gerhard

[swuehl]

Maybe like this?

=Date(monthstart([Posted Date]-10,1)+9)

[gerhardl]

That doesn't seem to work, I get null values.

Could you maybe incorporate that into my full expression, including the date formats and the Else Condition? I'm doing something wrong.

Thanks.

[swuehl · Accepted solution]

Ok, if your Posted Date is just a string, you'll need to interprete it:

=Date(monthstart( DATE#( [Posted Date], 'DD-MMM-YY') -10,1)+9,'YYYY/MM/DD')

[gerhardl]

It's Home Time here - I will check this tomorrow morning and respond.

Thanks for the help so far.