topic Calculating a future date in QlikView

Calculating a future date

Wed, 23 Jan 2013 15:46:14 GMT

Hi all,

I am trying to calculate a date field for each row in my dataset. Each row represents a live customer that has a contract start date. Depending on the customer, the contract length can vary between 1 - 24 months. If the customer appears in the list, it means they are currently a live customer and there contract may have auto renewed many times already between the start date and the current date. If for example a contract started on 01/01/20102 and is 4 months at a time, then it would have auto renewed 3 times already assuming today is the 23/01/2013 in Apr 2012, Aug 2012, & Dec 2012 and my next auto renewal date would be 01/04/2013.

Essentially, for each row I want to calculate the next auto renewal date beyond todays date. In the attached example qvw, I want to calculate Target Date.

Many Thanks

Haider

Re: Calculating a future date

Wed, 23 Jan 2013 17:39:12 GMT

Hi,

I attached an example of what can you do to resolve your problem.

I hope this can help you

Regards

Re: Calculating a future date

Wed, 23 Jan 2013 19:03:46 GMT

I dont know if its better but in my experience I prefer use some qlikview function rather than Macros, I use an AddMonth() function in the load script to resolve this issue.

Regards

Re: Calculating a future date

wms_manis — Wed, 23 Jan 2013 20:15:31 GMT

Hi Haider,

Addmonths(date,#) is the way to go.

You can do this in script or in the expression.

I have attached a calendar builder that I added a straight table to. The first dimension is the starting date.

The expression is addmonth(date,4) to calculate 4 months ahead.

Wade

Re: Calculating a future date

Anonymous — Wed, 23 Jan 2013 22:34:17 GMT

Hi,

please find attachment of qvw,let you know.

Re: Calculating a future date

Thu, 24 Jan 2013 05:40:32 GMT

Hi ,

Check out the Attachment..

Hope it helps you..

Re: Calculating a future date

Thu, 24 Jan 2013 06:03:47 GMT

Thanks for all the responses. The answer was a little more than just MonthAdd becasue I wanted to know the next upcoming renewal post todays date. Using MonthAdd alone could give me a past renewal date. The purpose is to contact those customers 1-2 months before there renewal date is due to build the customer relationship and stop them from cancelling the auto renewal. QlikCommunity is awesome!!