New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Contributor II

Calculate next Cycle Date

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

Gerhard

Tags (1)
1 Solution

Accepted Solutions
MVP

Re: Calculate next Cycle Date

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

4 Replies
MVP

Re: Calculate next Cycle Date

Maybe like this?

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

Contributor II

Re: Calculate next Cycle Date

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.

MVP

Re: Calculate next Cycle Date

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

Contributor II

Re: Calculate next Cycle Date

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

Thanks for the help so far.