Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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