topic Re: Calculate next Cycle Date in QlikView

Calculate next Cycle Date

gerhardl — Mon, 05 Nov 2012 14:33:44 GMT

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

Re: Calculate next Cycle Date

swuehl — Mon, 05 Nov 2012 14:44:59 GMT

Maybe like this?

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

Re: Calculate next Cycle Date

gerhardl — Mon, 05 Nov 2012 15:34:27 GMT

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.

Re: Calculate next Cycle Date

swuehl — Mon, 05 Nov 2012 15:48:55 GMT

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')

Re: Calculate next Cycle Date

gerhardl — Mon, 05 Nov 2012 15:51:21 GMT

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

Thanks for the help so far.