Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Months between date and apply amount to months between 2 dates

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:

CustomerYearMonthAmount
Customer X2014-02500
Customer X2014-03500
Customer X2014-04500
Customer X2014-05500

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

1 Solution

Accepted Solutions
Nicole-Smith

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.

View solution in original post

2 Replies
Nicole-Smith

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
Not applicable
Author

Thanks Nicole. Really useful.

Regards,

Henco