topic Re: Finding months between two dates in QlikView

Finding months between two dates

Mon, 19 Jan 2015 11:29:09 GMT

Hi All

I am struggling to solve my problem and would appreciate some help.

I have two date fields which are formatted differently in my source data.

One is a notification date which is formatted like so 01/01/2015

The other is report month which is formatted like this 201505

Now i need to find out the months between the two.

So it would be 4 months for the above.

I am hoping to work this out in the script as i want the answer to be a filed which i can use in my data and also create another field in my script which would be banding.

Any ideas.

Thanks

Andrew

Re: Finding months between two dates

PrashantSangle — Mon, 19 Jan 2015 11:37:14 GMT

Hi,

TRY LIKE

mONTH(date#(201505,'YYYYMM'))-MONTH(date#('01/01/2015','DD/MM/YYYY'))

Regards

Re: Finding months between two dates

Mon, 19 Jan 2015 11:56:20 GMT

Hi, Thanks for the answer, Unfortunately that doesn't work. The date which is actually formatted like 201505 is text not an actual date. So there is no code actually behind the text so as to do the sum.

I think what i will have to do is take the month and the year off the 201505 and create a date by sticking a 01 in front of it like 01/05/2015 then do the sum.. Im struggling with the formula though.

Re: Finding months between two dates

kiranmanoharrode — Mon, 19 Jan 2015 12:02:15 GMT

Hi,

TRY LIKE

Num(Month(date#(201505,'YYYYMM')))-Num(MONTH(date#('01/01/2015','DD/MM/YYYY')))

Regards

Kiran

Re: Finding months between two dates

PrashantSangle — Mon, 19 Jan 2015 12:05:38 GMT

Hi,

If it is text then Month(Date#(datefield,'YYYYMM')) will definately work for you

also try with right(datefield,2)

Regards

Re: Finding months between two dates

PradeepReddy — Mon, 19 Jan 2015 12:16:39 GMT

try something like this... using makedate() function

=num(month(makedate(left(201505,4),Right(201505,2),1)))-num(MONTH(date#('01/01/2015','DD/MM/YYYY')))

Re: Finding months between two dates

Anonymous — Mon, 19 Jan 2015 12:38:54 GMT

Hi,

Try below code in script:

LOAD Num(Month(Date(ReportDates)-Date(NotificationDates))) as Months;
LOAD
Date(Date#(NotificationDate,'DD/MM/YYYY'),'DD/MM/YYYY') as NotificationDates,
Date(makedate(left(ReportDate,4),Right(ReportDate,2),1),'DD/MM/YYYY') as ReportDates;
LOAD * Inline
[ReportDate,NotificationDate
201505,01/01/2015
];

Re: Finding months between two dates

Mon, 19 Jan 2015 15:05:55 GMT

Hi All,

Thanks for you help with this... i have found the answer...

Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY') as newdate,

NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))

Re: Finding months between two dates

Mon, 19 Jan 2015 15:09:23 GMT

I am now having a problem banding the above result. So i have found out how many working days between the two dates above.. but now i want to band the results by months.

So 0-6mts, 6-12mths, 12-18mts, 18-24mts, 24-36mts, >36mts

Below is my script but im getting errors say you are only allowed 3 if conditions.. I didnt know that..

Any help

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=6,'0-6mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >6 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=12,'6-12mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >12 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=18,'12-18mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >18 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=24,'18-24mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >24 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=36,'24-36',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >36,'>36mts','Error'))))))) as Banding