topic Re: How to find the Month between two dates in QlikView

How to find the Month between two dates

Anonymous — Mon, 07 Nov 2016 12:32:45 GMT

Hi Geniuses,

I have one issue , below is my data

Customer Start Date Term Amount

A	30/11/2016 20:00	36	1700000
B	15/06/2016 20:00	12	78000
C	31/08/2016 20:00	1	20000

I have a start date Term and Amount now what I want is three more columns in which if Start Date is 30/11/2016 and Term is 36 months then the one column will come as end date as 30/11/2019 and then in the other column this 36 months will come as Months(Nov-2016,Dec-2016,Jan-2017 and so on till 36 months) and then in one column the amount will get equally divided between the months and this goes for every client

As you can see the tenure is different and the start date is different for every client

Thanks in advance,

Re: How to find the Month between two dates

Gysbert_Wassenaar — Mon, 07 Nov 2016 12:37:56 GMT

Something like this:

LOAD

Customer,

AddMonths([Start Date], IterNo()-1) as [Start Date],

AddMonths([Start Date], IterNo()) as [End Date]

Term,

Amount,

Amount / Term as [Monthly Amount]

FROM

source_data

WHILE

IterNo() <= Term

;

Re: How to find the Month between two dates

its_anandrjs — Mon, 07 Nov 2016 12:44:07 GMT

Hi,

If in a script level you want to achieve this you can do this by using date functions (Monthend or date functions) or the for loop for creating the date data for the same.

Regards

Anand

Re: How to find the Month between two dates

Anonymous — Tue, 08 Nov 2016 04:59:42 GMT

You are awesome sir