Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hencovanee
Contributor 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

Re: Months between date and apply amount to months between 2 dates

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.

2 Replies

Re: Months between date and apply amount to months between 2 dates

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.

hencovanee
Contributor II

Re: Months between date and apply amount to months between 2 dates

Thanks Nicole. Really useful.

Regards,

Henco

Community Browser