New field to show Month date based on Start and End date

[alec1982]

Hi,

I have a table that contain:

ID     Expense          Start Date               End Date          Freq

1          $10               2011-06-01             2012-06-31       Monthly

1          $4                 2011-05-1               2011-05-1          OneTime

2          $20               2012-07-01              2014-07-31       Monthly

Looking at the start date and end date, Is there anyway i can have a column to show the Year and month so i can have a row fr each payement.

Like:

ID     Expense          Payment Date          

1          $10               2011-06-01

1          $10              2011-07-01

1          $10               2011-08-01

...

....

...

..

.

1          $10              2012-06-01

The reason I am asking for that is because I need to show the expenses per year.

Thanks for your help

[alec1982]

Hi,

This helped me a lot. however I still have one thing that I couldn't understand.

When frequency is Annually, and Start Date 2007-01-01 and End Date 2007-12-31 I get payment date 2007-12-01

while It should be 2007-01-01.

When Frequency = Quarterly, Start date= 1999-12-01 and end date 2012-05-31 I get the first payment date 2000-02-01 and it increment by three months. while it should start on the starting date 1999-12-01 and then increment by 3 months.

Is there a solution for that?

Again, I appreciate your help,

Alec

[swuehl]

I assume you are using something like

FreqNum*iterno()-1

instead of

FreqNum*(iterno()-1)

[alec1982]

Hi,

The only difference I have is that I am setting the payment date to start on MonthStart which should not have impact on it.

The result I am getting are correct when the frequency is Monthly. but when it is quarterly it is adding the payment date on the last month of the quarter and same thing when the frequency is yearly it is adding the payment on the last month of the year.

Is there anyway I can have the payment date to be on the starting month of the quarter and the starting month of the year.

Thxs,

Here is the script:

tblExpenseSchedule2:

LOAD    

    ExpenseId,

    ExpenseStartDate,

    ExpenseEndDate,

    Date(MonthStart(AddMonths(ExpenseStartDate,ExpenseFrequencyNum*iterno()-1)),'YYYY-MM-DD') as [Payment Date],

    ExpenseFrequency,

    ExpenseFrequencyNum,

Resident tblExpenseSchedule1

while ExpenseEndDate >  MonthStart(addmonths(ExpenseStartDate, ExpenseFrequencyNum*iterno()-1));

[swuehl]

Like I said, you are not using

FreqNum*(iterno()-1)

(notice the parantheses)

[alec1982]

Hi,

Thank you so much for clearing that up!

I totaly didn't see it. now I have the exact requiered Payment dates.

Thxs,

[alec1982]

Hi Swuehl,

Sorry to interrupt you again.

I am getting wrong date when the frequency = 'One time'.

If I put 1 for One time frequency it adds up like monthly and that brings more dates as in the example below.

ID      Start                End               Frequency     Payment

1       10/03/2010       12/11/2012       One Time     10/03/2010

1        10/03/2010     12/11/2012        One Time     11/03/2010

...     .                                                                 12/03/2010

..

...

...                                                                       12/03/2012  

While What is needed when the frequency is One time is to show up only on Payment date which is the same as the starting date.

Again, thank you for your help!  

[swuehl]

For one-time payments, I would use 1, and make sure that your start equals your end date (or the period is smaller than one month).

I guess it's not an option to change the start and end date. Maybe it's easiest to set a FreqNum that is larger than the maximum possible period (maybe like 100 years*12 Month = 1200). Though I normally don't like that magic numbers.

You could also just use conditionals to handle the one time payment separately.