topic Re: Date Difference in QlikView

Date Difference

Fri, 08 May 2015 10:43:17 GMT

I have a Two Column Date of Joining and Date of Resignation ...

Example :Date of Joining - 01/01/2012 And Date of Resignation - 02/05/2015

so i want the Out Put how many Year Month and Day Employee works.. like .. 3 year 4 Month and 1 Day ...

Plz Tell me

Re: Date Difference

Fri, 08 May 2015 10:51:35 GMT

Hi Brijesh,

have a look at the interval function, this should help you in creating this

Joe

Re: Date Difference

nagarjuna_kotha — Fri, 08 May 2015 11:26:15 GMT

Hi,

Can u share Sample file to better understand ?

Regards,

Nagarjuna

Re: Date Difference

tyagishaila — Fri, 08 May 2015 11:47:05 GMT

You can try it,

Day(EndDate)- Day(StartDate)

Month(EndDate)- Month(StartDate)

Year(EndDate)- Year(StartDate)

Re: Date Difference

sujeetsingh — Fri, 08 May 2015 11:53:08 GMT

Use Interval() function of Qlik

Re: Date Difference

anbu1984 — Fri, 08 May 2015 12:14:29 GMT

Load *,Floor(Days/365) & ' Years ' & Floor(Mod(Days,365)/30) & ' Months ' & Mod(Mod(Days,365),30) & ' Days ';
Load *,Interval(DOR-DOJ,'DD') As Days;
lOAD Date#(DOJ,'DD/MM/YYYY') As DOJ,Date#(DOR,'DD/MM/YYYY') As DOR Inline [
DOJ,DOR
01/01/2012,02/05/2015 ];

Re: Date Difference

Fri, 08 May 2015 18:15:05 GMT

Thanks Anbu

can u as well help me in your code if i have DOJ column and DOR column...

Load *,Floor(Days/365) & ' Years ' & Floor(Mod(Days,365)/30) & ' Months ' &Mod(Mod(Days,365),30) & ' Days ';
Load *,Interval(DOR-DOJ,'DD') As Days;
lOAD Date#(DOJ,'DD/MM/YYYY') As DOJ,Date#(DOR,'DD/MM/YYYY') As DOR Inline [
DOJ,DOR
01/01/2012,02/05/2015 ];

so instead of writing date 01/01/2012,02/05/2015 ]; what i need to write..

so sry ,,i m new in qlikview so i m getting doubt..

Re: Date Difference

Fri, 08 May 2015 18:18:52 GMT

But how to write code.. i have a data like

Date of Joining Date of Resignation Total Number of Period Person worked

01/02/2012 05/06/2014

04/12/2011 06/01/2015

i want to Out put to show like total Period worked i.e 3 Year 6 Month 4 day...

Re: Date Difference

Fri, 08 May 2015 18:19:32 GMT

But how to write code.. i have a data like

Date of Joining Date of Resignation Total Number of Period Person worked

01/02/2012 05/06/2014

04/12/2011 06/01/2015

i want to Out put to show like total Period worked i.e 3 Year 6 Month 4 day...

Re: Date Difference

anbu1984 — Sat, 09 May 2015 13:13:20 GMT

What is your input? Excel or Database?

Re: Date Difference

Sat, 09 May 2015 14:36:00 GMT

Hello Brijesh,

in you script(Where you load your data) add a column and based on Anbu's add the below.

Floor(Interval([Date of Joining]-[Date of Resignation],'DD')/365) & ' Years ' & Floor(Mod(Interval([Date of Joining]-[Date of Resignation],'DD'),365)/30) & ' Months ' & Mod(Mod(Interval([Date of Joining]-[Date of Resignation],'DD'),365),30) & ' Days ' as [Total Number of Period Person worked]

hop this helps

Re: Date Difference

Mon, 11 May 2015 04:30:43 GMT

Excel ... I have a Two column .. one of Date of Joining and Second of Date of Resignation.. so i need to find out total

how many period employee worked..

Kindly tell me... Thanks

Re: Date Difference

anbu1984 — Mon, 11 May 2015 05:19:33 GMT

Load *,Floor(Days/365) & ' Years ' & Floor(Mod(Days,365)/30) & ' Months ' &Mod(Mod(Days,365),30) & ' Days ';
Load *,Interval(DOR-DOJ,'DD') As Days;
lOAD Date#(DOJ,'DD/MM/YYYY') As DOJ,Date#(DOR,'DD/MM/YYYY') As DOR;

Load DOJ,DOR From Excel.xlsx; //Edit Script --> Table Files --> File Wizard -- This will automatically create load statement

Re: Date Difference

Mon, 11 May 2015 07:09:55 GMT

so first i need to load my date data

Load DOJ,DOR,

Date#(DOJ,'DD/MM/YYYY') As DOJ1,

Date#(DOR,'DD/MM/YYYY') As DOR1,

Interval(DOR1-DOJ1,'DD') As Days

From Excel.xlsx;

BUT while Reloading its giving me a Error Field not found - <DOR1>...

kinldy help me how to write this code....

Re: Date Difference

anbu1984 — Mon, 11 May 2015 08:47:45 GMT

Load DOJ,DOR,

Date#(DOJ,'DD/MM/YYYY') As DOJ1,

Date#(DOR,'DD/MM/YYYY') As DOR1,

Interval(DOR1-DOJ1,'DD') As Days //You are getting error since DOR1 is not present in your input excel

From Excel.xlsx;

Calculate difference in preceding load

Load *, Interval(DOR1-DOJ1,'DD') As Days; //Preceding Load

Load DOJ,DOR,

Date#(DOJ,'DD/MM/YYYY') As DOJ1,

Date#(DOR,'DD/MM/YYYY') As DOR1

From Excel.xlsx;

Re: Date Difference

Mon, 11 May 2015 09:07:01 GMT

if i m doing this ways its not showing any data of Days:

DOJ	DOR	DOJ1	DOR1	Days
2/4/2012	3/2/2015	41001	42038	-
3/4/2012	4/2/2015	41002	42039	-
6/5/2012	5/6/2015	41035	42160	-
9/6/2012	6/9/2015	41069	42253	-
12/9/2012	9/12/2015	41164	42347	-
4/10/2012	10/4/2015	41186	42104	-
5/1/2015	8/5/2015	42009	42132	-
3/7/2015	7/3/2015	42188	42070	-
3/7/2015	8/5/2015	42188	42132	-
5/8/2015	8/5/2015	42221	42132	-

Re: Date Difference

Mon, 11 May 2015 09:08:41 GMT

its giving me a Data.. But I have date format DD/MM/YYYY.. and its giving a Data in MM/DD/YYYY Format...kinldy help Me

Re: Date Difference

anbu1984 — Mon, 11 May 2015 09:31:19 GMT

Can you post sample excel file

Re: Date Difference

Mon, 11 May 2015 10:35:21 GMT

DOJ	DOR
3/12/2011	29/05/2012
1/12/2013	3/5/2015
4/4/2011	6/7/2014
29/01/2013	31/12/2014
1/5/2011	5/5/2014

Re: Date Difference

anbu1984 — Mon, 11 May 2015 10:43:52 GMT

When I pasted above data into excel, some of the rows have date and others are general. Its better you keep data in one format.

Can you attach sample excel file.