4 Replies Latest reply: Nov 5, 2012 10:51 AM by Gerhard Laubscher RSS

    Calculate next Cycle Date

    Gerhard Laubscher

      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,