Calculating Months Between Two Dates

[sbeaty]

Hello. I am trying to create an expression or do something in the load editor to get what I want, which is...

I have two dates. EffectiveDate and ExpirationDate. I want to know all the months between those two dates and have each month be it's own column. That way in my table I can see how much money is due for that particular month and future months ahead and how much is due from the individual level too. 

Something like this..

                                     Jan          Feb       March      April       May

Unique ID                14.43     14.43     31.32       42.12      21.67            Total 

Unique ID                 64.94     21.74     55.97      11.57      67.32             Total

Grand Total

Thank you for your help!

[rwunderlich]

Something like:

NewMonths:
Load
  Id,
  AddMonths(EffectiveData, IterNo()-1) as Month
Resident mytable
While AddMonths(EffectiveData, IterNo()-1) <= ExpirationDate;

-Rob

[sbeaty]

That almost works, just the Grand total is the same for every month. Should be giving me what's due for each month with a grand total.

[rwunderlich]

How do you allocate amounts to the generated months?

-Rob