Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sbeaty
Contributor II
Contributor II

Calculating Months Between Two Dates

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!

3 Replies
rwunderlich

Something like:

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

-Rob

sbeaty
Contributor II
Contributor II
Author

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