Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
hencovanee
Creator II
Creator II

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.

View solution in original post

hencovanee
Creator II
Creator II
Author

Thanks Nicole. Really useful.

Regards,

Henco