Months between date and apply amount to months between 2 dates

[Anonymous]

Hi all,

In the attached QV doc I add some example data for the question I have.

In the document I have two dates: a startdate and an enddate. I would like to apply the total amount evenly to the months between the two dates. Like this:

Customer	YearMonth	Amount
Customer X	2014-02	500
Customer X	2014-03	500
Customer X	2014-04	500
Customer X	2014-05	500

And so on for the other two customers in de example doc.

I hope someone can help me with this. Thanks in advance.

Kind regards,

Henco

[Nicole-Smith · Accepted solution]

Something like this should do the trick:

Data:

LOAD Customer,Amount,date(MonthStart(Startdate,IterNo()-1),'YYYY-MM') as YearMonth

INLINE [

    Customer, Amount, Startdate, Enddate

    Customer X, 2000, 01-02-2014, 15-05-2014

    Customer Y, 3000, 05-03-2014, 08-08-2014

    Customer Z, 5000, 08-03-2014, 31-12-2014

]

while AddMonths(MonthStart(Startdate),IterNo()-1) <= MonthStart(Enddate);

I've also attached a working example.

[Anonymous]

Thanks Nicole. Really useful.

Regards,

Henco