topic Re: month difference in QlikView

month difference

Thu, 04 Dec 2014 10:36:15 GMT

hi,

I have a table in which I have 2 columns like these :

01/09/2002	200209
01/10/2002	200209
01/12/2002	200209
01/01/2003	200209
01/02/2003	200209
01/03/2003	200209
01/04/2003	200209
01/08/2003	200209
01/09/2003	200209
01/11/2003	200209
01/12/2003	200209
01/01/2004	200209
01/03/2004	200209
01/05/2004	200209
01/06/2004	200209
01/08/2004	200209
01/09/2002	200209
01/12/2002	200209

Now I want to show the difference (in months) between the two, how can I achieve this?

Re: month difference

sushil353 — Thu, 04 Dec 2014 10:39:20 GMT

Hi,

Try This:

Month(Column1)-Month(Date(Date#(Column2,YYYYMM)))

HTH

Sushil

Re: month difference

jonathandienst — Thu, 04 Dec 2014 10:56:45 GMT

You need to take years into account with something like:

=(Year(Column1) * 12 + Month(Column1)) - (Left(Column2, 4) * 12 + Right(Column2, 2))

HTH

Jonathan

Re: month difference

its_anandrjs — Thu, 04 Dec 2014 11:03:13 GMT

Hi,

Try this ways

LOAD

Date(Date#(Date1,'DD/MM/YYYY'),'MM/YYYY') as NewDate1,

Date(Date#(Date2,'YYYYMM'),'MM/YYYY') as NewDate2,

Date(Date(Date#(Date1,'DD/MM/YYYY'),'MM/YYYY') - Date(Date#(Date2,'YYYYMM')),'MM') as NewDiff;

LOAD * Inline [

Date1,Date2

01/09/2002, 200209

01/10/2002, 200209

01/12/2002, 200209

01/01/2003, 200209

01/02/2003, 200209

01/03/2003, 200209

01/04/2003, 200209

01/08/2003, 200209

01/09/2003, 200209

01/11/2003, 200209

01/12/2003, 200209

01/01/2004, 200209

01/03/2004, 200209

01/05/2004, 200209

01/06/2004, 200209

01/08/2004, 200209

01/09/2002, 200209

01/12/2002, 200209 ];

Regards

Anand

Re: month difference

Thu, 04 Dec 2014 11:05:08 GMT

hi Jonathan,

I had used this expression in the script, it errored out.

If I display existing columns as dimensions in a pivot table, I can't see them.

Is there a way to add a calculated dimension in a table box?

Re: month difference

MarcoWedel — Thu, 04 Dec 2014 12:33:48 GMT

Hi,

one solution could be:

LOAD *,
    Month(date2)-Month(date1)+(Year(date2)-Year(date1))*12 as MonthDiff;
LOAD MonthName(Date#(date2,'DD/MM/YYYY')) as date2,
    MonthName(Date#(date1,'YYYYMM')) as date1
INLINE [
    date2, date1
    01/09/2002, 200209
    01/10/2002, 200209
    01/12/2002, 200209
    01/01/2003, 200209
    01/02/2003, 200209
    01/03/2003, 200209
    01/04/2003, 200209
    01/08/2003, 200209
    01/09/2003, 200209
    01/11/2003, 200209
    01/12/2003, 200209
    01/01/2004, 200209
    01/03/2004, 200209
    01/05/2004, 200209
    01/06/2004, 200209
    01/08/2004, 200209
    01/09/2002, 200209
    01/12/2002, 200209
];

hope this helps

regards

Marco

Re: month difference

its_anandrjs — Thu, 04 Dec 2014 13:19:56 GMT

Hi,

There is another way for the doing the same problem is by

LOAD *,
Year(NewDate1) - Year(NewDate2) as YearDiff,
Month(NewDate1) - Month(NewDate2) as MonthDiff,
(Year(NewDate1) - Year(NewDate2)) * 12 +
Month(NewDate1) - Month(NewDate2) as TotalMonDiff;

Load *,
Date(Date#(Date1,'DD/MM/YYYY'),'MM/YYYY') as NewDate1,
Date(Date#(Date2,'YYYYMM'),'MM/YYYY') as NewDate2;

LOAD * INLINE [
Date1, Date2
01/09/2002, 200209
01/10/2002, 200209
01/12/2002, 200209
01/01/2003, 200209
01/02/2003, 200209
01/03/2003, 200209
01/04/2003, 200209
01/08/2003, 200209
01/09/2003, 200209
01/11/2003, 200209
01/12/2003, 200209
01/01/2004, 200209
01/03/2004, 200209
01/05/2004, 200209
01/06/2004, 200209
01/08/2004, 200209
01/09/2002, 200209
01/12/2002, 200209
];

And you get table as

Regards

Anand